SQLite Version 3 Overview

  • bugs
  • changes
  • contrib
  • cvs repository
  • documentation
  • download
  • faq
  • home
  • mailing list
  • news
  • quick start
  • support
  • syntax
  • timeline
  • wiki

SQLite Version 3 Overview

SQLite version 3.0 introduces important changes to the library, including:

  • A more compact format for database files.
  • Manifest typing and BLOB support.
  • Support for both UTF-8 and UTF-16 text.
  • User-defined text collating sequences.
  • 64-bit ROWIDs.
  • Improved Concurrency.

This document is a quick introduction to the changes for SQLite 3.0 for users who are already familiar with SQLite version 2.8.

Naming Changes

SQLite version 2.8 will continue to be supported with bug fixes for the foreseeable future. In order to allow SQLite version 2.8 and SQLite version 3.0 to peacefully coexist, the names of key files and APIs in SQLite version 3.0 have been changed to include the character "3". For example, the include file used by C programs has been changed from "sqlite.h" to "sqlite3.h". And the name of the shell program used to interact with databases has been changed from "sqlite.exe" to "sqlite3.exe". With these changes, it is possible to have both SQLite 2.8 and SQLite 3.0 installed on the same system at the same time. And it is possible for the same C program to link against both SQLite 2.8 and SQLite 3.0 at the same time and to use both libraries at the same time.

New File Format

The format used by SQLite database files has been completely revised. The old version 2.1 format and the new 3.0 format are incompatible with one another. Version 2.8 of SQLite will not read a version 3.0 database files and version 3.0 of SQLite will not read a version 2.8 database file.

To convert an SQLite 2.8 database into an SQLite 3.0 database, have ready the command-line shells for both version 2.8 and 3.0. Then enter a command like the following:

sqlite OLD.DB .dump | sqlite3 NEW.DB

The new database file format uses B+trees for tables. In a B+tree, all data is stored in the leaves of the tree instead of in both the leaves and the intermediate branch nodes. The use of B+trees for tables allows for better scalability and the storage of larger data fields without the use of overflow pages. Traditional B-trees are still used for indices.

The new file format also supports variable pages sizes between 512 and 32768 bytes. The size of a page is stored in the file header so the same library can read databases with different pages sizes, in theory, though this feature has not yet been implemented in practice.

The new file format omits unused fields from its disk images. For example, indices use only the key part of a B-tree record and not the data. So for indices, the field that records the length of the data is omitted. Integer values such as the length of key and data are stored using a variable-length encoding so that only one or two bytes are required to store the most common cases but up to 64-bits of information can be encoded if needed. Integer and floating point data is stored on the disk in binary rather than being converted into ASCII as in SQLite version 2.8. These changes taken together result in database files that are typically 25% to 35% smaller than the equivalent files in SQLite version 2.8.

Details of the low-level B-tree format used in SQLite version 3.0 can be found in header comments to the btree.c source file.

Manifest Typing and BLOB Support

SQLite version 2.8 will deal with data in various formats internally, but when writing to the disk or interacting through its API, SQLite 2.8 always converts data into ASCII text. SQLite 3.0, in contrast, exposes its internal data representations to the user and stores binary representations to disk when appropriate. The exposing of non-ASCII representations was added in order to support BLOBs.

SQLite version 2.8 had the feature that any type of data could be stored in any table column regardless of the declared type of that column. This feature is retained in version 3.0, though in a slightly modified form. Each table column will store any type of data, though columns have an affinity for the format of data defined by their declared datatype. When data is inserted into a column, that column will make at attempt to convert the data format into the columns declared type. All SQL database engines do this. The difference is that SQLite 3.0 will still store the data even if a format conversion is not possible.

For example, if you have a table column declared to be of type "INTEGER" and you try to insert a string, the column will look at the text string and see if it looks like a number. If the string does look like a number it is converted into a number and into an integer if the number does not have a fractional part, and stored that way. But if the string is not a well-formed number it is still stored as a string. A column with a type of "TEXT" tries to convert numbers into an ASCII-Text representation before storing them. But BLOBs are stored in TEXT columns as BLOBs because you cannot in general convert a BLOB into text.

In most other SQL database engines the datatype is associated with the table column that holds the data - with the data container. In SQLite 3.0, the datatype is associated with the data itself, not with its container. Paul Graham in his book ANSI Common Lisp calls this property "Manifest Typing". Other writers have other definitions for the term "manifest typing", so beware of confusion. But by whatever name, that is the datatype model supported by SQLite 3.0.

Additional information about datatypes in SQLite version 3.0 is available separately.

Support for UTF-8 and UTF-16

The new API for SQLite 3.0 contains routines that accept text as both UTF-8 and UTF-16 in the native byte order of the host machine. Each database file manages text as either UTF-8, UTF-16BE (big-endian), or UTF-16LE (little-endian). Internally and in the disk file, the same text representation is used everywhere. If the text representation specified by the database file (in the file header) does not match the text representation required by the interface routines, then text is converted on-the-fly. Constantly converting text from one representation to another can be computationally expensive, so it is suggested that programmers choose a single representation and stick with it throughout their application.

In the current implementation of SQLite, the SQL parser only works with UTF-8 text. So if you supply UTF-16 text it will be converted. This is just an implementation issue and there is nothing to prevent future versions of SQLite from parsing UTF-16 encoded SQL natively.

When creating new user-defined SQL functions and collating sequences, each function or collating sequence can specify it if works with UTF-8, UTF-16be, or UTF-16le. Separate implementations can be registered for each encoding. If an SQL function or collating sequences is required but a version for the current text encoding is not available, then the text is automatically converted. As before, this conversion takes computation time, so programmers are advised to pick a single encoding and stick with it in order to minimize the amount of unnecessary format juggling.

SQLite is not particular about the text it receives and is more than happy to process text strings that are not normalized or even well-formed UTF-8 or UTF-16. Thus, programmers who want to store IS08859 data can do so using the UTF-8 interfaces. As long as no attempts are made to use a UTF-16 collating sequence or SQL function, the byte sequence of the text will not be modified in any way.

User-defined Collating Sequences

A collating sequence is just a defined order for text. When SQLite 3.0 sorts (or uses a comparison operator like "<" or ">=") the sort order is first determined by the data type.

  • NULLs sort first
  • Numeric values sort next in numerical order
  • Text values come after numerics
  • BLOBs sort last

Collating sequences are used for comparing two text strings. The collating sequence does not change the ordering of NULLs, numbers, or BLOBs, only text.

A collating sequence is implemented as a function that takes the two strings being compared as inputs and returns negative, zero, or positive if the first string is less than, equal to, or greater than the second. SQLite 3.0 comes with a single built-in collating sequence named "BINARY" which is implemented using the memcmp() routine from the standard C library. The BINARY collating sequence works well for English text. For other languages or locales, alternative collating sequences may be preferred.

The decision of which collating sequence to use is controlled by the COLLATE clause in SQL. A COLLATE clause can occur on a table definition, to define a default collating sequence to a table column, or on field of an index, or in the ORDER BY clause of a SELECT statement. Planned enhancements to SQLite are to include standard CAST() syntax to allow the collating sequence of an expression to be defined.

64-bit ROWIDs

Every row of a table has a unique rowid. If the table defines a column with the type "INTEGER PRIMARY KEY" then that column becomes an alias for the rowid. But with or without an INTEGER PRIMARY KEY column, every row still has a rowid.

In SQLite version 3.0, the rowid is a 64-bit signed integer. This is an expansion of SQLite version 2.8 which only permitted rowids of 32-bits.

To minimize storage space, the 64-bit rowid is stored as a variable length integer. Rowids between 0 and 127 use only a single byte. Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three bytes. And so forth. Negative rowids are allowed but they always use nine bytes of storage and so their use is discouraged. When rowids are generated automatically by SQLite, they will always be non-negative.

Improved Concurrency

SQLite version 2.8 allowed multiple simultaneous readers or a single writer but not both. SQLite version 3.0 allows one process to begin writing the database while other processes continue to read. The writer must still obtain an exclusive lock on the database for a brief interval in order to commit its changes, but the exclusive lock is no longer required for the entire write operation. A more detailed report on the locking behavior of SQLite version 3.0 is available separately.

A limited form of table-level locking is now also available in SQLite. If each table is stored in a separate database file, those separate files can be attached to the main database (using the ATTACH command) and the combined databases will function as one. But locks will only be acquired on individual files as needed. So if you redefine "database" to mean two or more database files, then it is entirely possible for two processes to be writing to the same database at the same time. To further support this capability, commits of transactions involving two or more ATTACHed database are now atomic.

Credits

SQLite version 3.0 is made possible in part by AOL developers supporting and embracing great Open-Source Software.

This page last modified on 2006/03/03 21:39:54


Query Language Understood by SQLite: ALTER TABLE

  • cvs repository
  • documentation
  • download
  • faq
  • home
  • mailing list
  • news
  • quick start
  • support
  • syntax
  • timeline
  • wiki

SQL As Understood By SQLite

[Contents]

ALTER TABLE

sql-statement ::= | ALTER TABLE [database-name .] table-name alteration
alteration ::= | RENAME TO new-table-name
alteration ::= | ADD [COLUMN] column-def

SQLite's version of the ALTER TABLE command allows the user to rename or add a new column to an existing table. It is not possible to remove a column from a table.

The RENAME TO syntax is used to rename the table identified by [database-name.]table-name to new-table-name. This command cannot be used to move a table between attached databases, only to rename a table within the same database.

If the table being renamed has triggers or indices, then these remain attached to the table after it has been renamed. However, if there are any view definitions, or statements executed by triggers that refer to the table being renamed, these are not automatically modified to use the new table name. If this is required, the triggers or view definitions must be dropped and recreated to use the new table name by hand.

The ADD [COLUMN] syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. Column-def may take any of the forms permissable in a CREATE TABLE statement, with the following restrictions:

  • The column may not have a PRIMARY KEY or UNIQUE constraint.
  • The column may not have a default value of CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
  • If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

The execution time of the ALTER TABLE command is independent of the amount of data in the table. The ALTER TABLE command runs as quickly on a table with 10 million rows as it does on a table with 1 row.

After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 and earlier until the database is VACUUMed.

This page last modified on 2006/03/15 16:26:10


Query Language Understood by SQLite: ANALYZE

  • faq
  • home
  • mailing list
  • news
  • quick start
  • support
  • syntax
  • timeline
  • wiki

SQL As Understood By SQLite

[Contents]

ANALYZE

sql-statement ::= ANALYZE
sql-statement ::= ANALYZE database-name
sql-statement ::= ANALYZE [database-name .] table-name

The ANALYZE command gathers statistics about indices and stores them in a special tables in the database where the query optimizer can use them to help make better index choices. If no arguments are given, all indices in all attached databases are analyzed. If a database name is given as the argument, all indices in that one database are analyzed. If the argument is a table name, then only indices associated with that one table are analyzed.

The initial implementation stores all statistics in a single table named sqlite_stat1. Future enhancements may create additional tables with the same name pattern except with the "1" changed to a different digit. The sqlite_stat1 table cannot be DROPped, but it all the content can be DELETEd which has the same effect.


Query Language Understood by SQLite: ATTACH DATABASE

  • news
  • quick start
  • support
  • syntax
  • timeline
  • wiki

SQL As Understood By SQLite

[Contents]

ATTACH DATABASE

sql-statement ::= ATTACH [DATABASE] database-filename AS database-name

The ATTACH DATABASE statement adds a preexisting database file to the current database connection. If the filename contains punctuation characters it must be quoted. The names 'main' and 'temp' refer to the main database and the database used for temporary tables. These cannot be detached. Attached databases are removed using the DETACH DATABASE statement.

You can read from and write to an attached database and you can modify the schema of the attached database. This is a new feature of SQLite version 3.0. In SQLite 2.8, schema changes to attached databases were not allowed.

You cannot create a new table with the same name as a table in an attached database, but you can attach a database which contains tables whose names are duplicates of tables in the main database. It is also permissible to attach the same database file multiple times.

Tables in an attached database can be referred to using the syntax database-name.table-name. If an attached table doesn't have a duplicate table name in the main database, it doesn't require a database name prefix. When a database is attached, all of its tables which don't have duplicate names become the 'default' table of that name. Any tables of that name attached afterwards require the table prefix. If the 'default' table of a given name is detached, then the last table of that name attached becomes the new default.

Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:". If the main database is ":memory:" then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not. Atomic commit of attached databases is a new feature of SQLite version 3.0. In SQLite version 2.8, all commits to attached databases behaved as if the main database were ":memory:".

There is a compile-time limit of 10 attached database files.


Query Language Understood by SQLite: BEGIN TRANSACTION

  • syntax
  • timeline
  • wiki

SQL As Understood By SQLite

[Contents]

BEGIN TRANSACTION

sql-statement ::= BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [name]]
sql-statement ::= END [TRANSACTION [name]]
sql-statement ::= COMMIT [TRANSACTION [name]]
sql-statement ::= ROLLBACK [TRANSACTION [name]]

Beginning in version 2.0, SQLite supports transactions with rollback and atomic commit.

The optional transaction name is ignored. SQLite currently does not allow nested transactions.

No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed at the conclusion of the command.

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.

In SQLite version 3.0.8 and later, transactions can be deferred, immediate, or exclusive. Deferred means that no locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, you are guaranteed that no other thread or process will be able to read or write the database until the transaction is complete.

A description of the meaning of SHARED, RESERVED, and EXCLUSIVE locks is available separately.

The default behavior for SQLite version 3.0.8 is a deferred transaction. For SQLite version 3.0.0 through 3.0.7, deferred is the only kind of transaction available. For SQLite version 2.8 and earlier, all transactions are exclusive.

The COMMIT command does not actually perform a commit until all pending SQL commands finish. Thus if two or more SELECT statements are in the middle of processing and a COMMIT is executed, the commit will not actually occur until all SELECT statements finish.

An attempt to execute COMMIT might result in an SQLITE_BUSY return code. This indicates that another thread or process had a read lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.


Query Language Understood by SQLite: comment

SQL As Understood By SQLite

[Contents]

comment

comment ::= | SQL-comment | C-comment
SQL-comment ::= | -- single-line
C-comment ::= | /* multiple-lines [*/]

Comments aren't SQL commands, but can occur in SQL queries. They are treated as whitespace by the parser. They can begin anywhere whitespace can be found, including inside expressions that span multiple lines.

SQL comments only extend to the end of the current line.

C comments can span any number of lines. If there is no terminating delimiter, they extend to the end of the input. This is not treated as an error. A new SQL statement can begin on a line after a multiline comment ends. C comments can be embedded anywhere whitespace can occur, including inside expressions, and in the middle of other SQL statements. C comments do not nest. SQL comments inside a C comment will be ignored.


Query Language Understood by SQLite: COPY

SQL As Understood By SQLite

COPY

sql-statement ::= COPY [ OR conflict-algorithm ] [database-name .] table-name FROM filename
[ USING DELIMITERS delim ]

The COPY command is available in SQLite version 2.8 and earlier. The COPY command has been removed from SQLite version 3.0 due to complications in trying to support it in a mixed UTF-8/16 environment. In version 3.0, the command-line shell contains a new command .import that can be used as a substitute for COPY.

The COPY command is an extension used to load large amounts of data into a table. It is modeled after a similar command found in PostgreSQL. In fact, the SQLite COPY command is specifically designed to be able to read the output of the PostgreSQL dump utility pg_dump so that data can be easily transferred from PostgreSQL into SQLite.

The table-name is the name of an existing table which is to be filled with data. The filename is a string or identifier that names a file from which data will be read. The filename can be the STDIN to read data from standard input.

Each line of the input file is converted into a single record in the table. Columns are separated by tabs. If a tab occurs as data within a column, then that tab is preceded by a baskslash "\" character. A baskslash in the data appears as two backslashes in a row. The optional USING DELIMITERS clause can specify a delimiter other than tab.

If a column consists of the character "\N", that column is filled with the value NULL.

The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use for this one command. See the section titled ON CONFLICT for additional information.

When the input data source is STDIN, the input can be terminated by a line that contains only a baskslash and a dot: "\.".


Query Language Understood by SQLite: CREATE INDEX

SQL As Understood By SQLite

CREATE INDEX

sql-statement ::= | CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] index-name ON table-name ( column-name [, column-name]* )
column-name ::= | name [ COLLATE collation-name] [ ASC | DESC ]

The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key. Each column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order, but the sort order is ignored in the current implementation. Sorting is always done in ascending order.

The COLLATE clause following each column name defines a collating sequence used for text entires in that column. The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating sequence is otherwise defined, the built-in BINARY collating sequence is used.

There are no arbitrary limits on the number of indices that can be attached to a single table, nor on the number of columns in an index.

If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error.

The exact text of each CREATE INDEX statement is stored in the sqlite_master or sqlite_temp_master table, depending on whether the table being indexed is temporary. Every time the database is opened, all CREATE INDEX statements are read from the sqlite_master table and used to regenerate SQLite's internal representation of the index layout.

If the optional IF NOT EXISTS clause is present and another index with the same name aleady exists, then this command becomes a no-op.

Indexes are removed with the DROP INDEX command.


Query Language Understood by SQLite: CREATE TABLE

SQL As Understood By SQLite

CREATE TABLE

sql-command ::= | CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] table-name ( column-def [, column-def]* [, constraint]* )
sql-command ::= | CREATE [TEMP | TEMPORARY] TABLE [database-name.] table-name AS select-statement
column-def ::= | name [type] [[CONSTRAINT name] column-constraint]*
type ::= | typename | typename ( number ) | typename ( number , number )
column-constraint ::= | NOT NULL [ conflict-clause ] | PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT] | UNIQUE [ conflict-clause ] | CHECK ( expr ) | DEFAULT value | COLLATE collation-name
constraint ::= | PRIMARY KEY ( column-list ) [ conflict-clause ] | UNIQUE ( column-list ) [ conflict-clause ] | CHECK ( expr )
conflict-clause ::= | ON CONFLICT conflict-algorithm

A CREATE TABLE statement is basically the keywords "CREATE TABLE" followed by the name of a new table and a parenthesized list of column definitions and constraints. The table name can be either an identifier or a string. Tables names that begin with "sqlite_" are reserved for use by the engine.

Each column definition is the name of the column followed by the datatype for that column, then one or more optional column constraints. The datatype for the column does not restrict what data may be put in that column. See Datatypes In SQLite Version 3 for additional information. The UNIQUE constraint causes an index to be created on the specified columns. This index must contain unique keys. The COLLATE clause specifies what text collating function to use when comparing text entries for the column. The built-in BINARY collating function is used by default.

The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant or a number. Starting with version 3.1.0, the default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant or number, it is literally inserted into the column whenever an INSERT statement that does not specify a value for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names "ROWID", "OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY column man also include the keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B-Tree keys are automatically generated. Additional detail on automatic B-Tree key generation is available separately.

If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible within that same database connection and is automatically deleted when the database connection is closed. Any indices created on a temporary table are also temporary. Temporary tables and indices are stored in a separate file distinct from the main database file.

If a <database-name> is specified, then the table is created in the named database. It is an error to specify both a <database-name> and the TEMP keyword, unless the <database-name> is "temp". If no database name is specified, and the TEMP keyword is not present, the table is created in the main database.

The optional conflict-clause following each constraint allows the specification of an alternative default constraint conflict resolution algorithm for that constraint. The default is abort ABORT. Different constraints within the same table may have different default conflict resolution algorithms. If an COPY, INSERT, or UPDATE command specifies a different conflict resolution algorithm, then that algorithm is used in place of the default algorithm specified in the CREATE TABLE statement. See the section titled ON CONFLICT for additional information.

CHECK constraints are supported as of version 3.3.0. Prior to version 3.3.0, CHECK constraints were parsed but not enforced.

There are no arbitrary limits on the number of columns or on the number of constraints in a table. The total amount of data in a single row is limited to about 1 megabytes in version 2.8. In version 3.0 there is no arbitrary limit on the amount of data in a row.

The CREATE TABLE AS form defines the table to be the result set of a query. The names of the table columns are the names of the columns in the result.

The exact text of each CREATE TABLE statement is stored in the sqlite_master table. Every time the database is opened, all CREATE TABLE statements are read from the sqlite_master table and used to regenerate SQLite's internal representation of the table layout. If the original command was a CREATE TABLE AS then then an equivalent CREATE TABLE statement is synthesized and store in sqlite_master in place of the original command. The text of CREATE TEMPORARY TABLE statements are stored in the sqlite_temp_master table.

If the optional IF NOT EXISTS clause is present and another table with the same name aleady exists, then this command becomes a no-op.

Tables are removed using the DROP TABLE statement.


Query Language Understood by SQLite: CREATE TRIGGER

SQL As Understood By SQLite

CREATE TRIGGER

sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ]
database-event ON [database-name .] table-name
trigger-action
sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF
database-event ON [database-name .] view-name
trigger-action
database-event ::= DELETE |
INSERT |
UPDATE |
UPDATE OF column-list
trigger-action ::= [ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ]
BEGIN
trigger-step ; [ trigger-step ; ]*
END
trigger-step ::= update-statement | insert-statement |
delete-statement | select-statement

The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations (the trigger-action) that are automatically performed when a specified database event (the database-event) occurs.

A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular database table occurs, or whenever an UPDATE of one or more specified columns of a table are updated.

At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies that the SQL statements specified as trigger-steps may be executed (depending on the WHEN clause) for each database row being inserted, updated or deleted by the statement causing the trigger to fire.

Both the WHEN clause and the trigger-steps may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on trigger-events for which they are relevant, as follows:

INSERT | NEW references are valid
UPDATE | NEW and OLD references are valid
DELETE | OLD references are valid

If a WHEN clause is supplied, the SQL statements specified as trigger-steps are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.

The specified trigger-time determines when the trigger-steps will be executed relative to the insertion, modification or removal of the associated row.

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT trigger-step. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then this conflict handling policy is used instead.

Triggers are automatically dropped when the table that they are associated with is dropped.

Triggers may be created on views, as well as ordinary tables, by specifying INSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is not an error to execute an INSERT, DELETE or UPDATE statement on the view, respectively. Thereafter, executing an INSERT, DELETE or UPDATE on the view causes the associated triggers to fire. The real tables underlying the view are not modified (except possibly explicitly, by a trigger program).

Example:

Assuming that customer records are stored in the "customers" table, and that order records are stored in the "orders" table, the following trigger ensures that all associated orders are redirected when a customer changes his or her address:

CREATE TRIGGER update_customer_address UPDATE OF address ON customers
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;

With this trigger installed, executing the statement:

UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';

causes the following to be automatically executed:

UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';

Note that currently, triggers may behave oddly when created on tables with INTEGER PRIMARY KEY fields. If a BEFORE trigger program modifies the INTEGER PRIMARY KEY field of a row that will be subsequently updated by the statement that causes the trigger to fire, then the update may not occur. The workaround is to declare the table with a PRIMARY KEY column instead of an INTEGER PRIMARY KEY column.

A special SQL function RAISE() may be used within a trigger-program, with the following syntax

raise-function ::= RAISE ( ABORT, error-message ) |
RAISE ( FAIL, error-message ) |
RAISE ( ROLLBACK, error-message ) |
RAISE ( IGNORE )

When one of the first three forms is called during trigger-program execution, the specified ON CONFLICT processing is performed (either ABORT, FAIL or ROLLBACK) and the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the user, along with the specified error message.

When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would of been executed are abandoned. No database changes are rolled back. If the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes execution at the beginning of the next step.

Triggers are removed using the DROP TRIGGER statement.


Query Language Understood by SQLite: CREATE VIEW

SQL As Understood By SQLite

CREATE VIEW

sql-command ::= CREATE [TEMP | TEMPORARY] VIEW [database-name.] view-name AS select-statement

The CREATE VIEW command assigns a name to a pre-packaged SELECT statement. Once the view is created, it can be used in the FROM clause of another SELECT in place of a table name.

If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "VIEW" then the view that is created is only visible to the process that opened the database and is automatically deleted when the database is closed.

If a <database-name> is specified, then the view is created in the named database. It is an error to specify both a <database-name> and the TEMP keyword, unless the <database-name> is "temp". If no database name is specified, and the TEMP keyword is not present, the table is created in the main database.

You cannot COPY, DELETE, INSERT or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use a TRIGGER on the view to accomplish the same thing. Views are removed with the DROP VIEW command.


Query Language Understood by SQLite: DELETE

SQL As Understood By SQLite

DELETE

sql-statement ::= DELETE FROM [database-name .] table-name [WHERE expr]

The DELETE command is used to remove records from a table. The command consists of the "DELETE FROM" keywords followed by the name of the table from which records are to be removed.

Without a WHERE clause, all rows of the table are removed. If a WHERE clause is supplied, then only those rows that match the expression are removed.


Query Language Understood by SQLite: DETACH DATABASE

SQL As Understood By SQLite

DETACH DATABASE

sql-command ::= DETACH [DATABASE] database-name

This statement detaches an additional database connection previously attached using the ATTACH DATABASE statement. It is possible to have the same database file attached multiple times using different names, and detaching one connection to a file will leave the others intact.

This statement will fail if SQLite is in the middle of a transaction.


Query Language Understood by SQLite: DROP INDEX

SQL As Understood By SQLite

DROP INDEX

sql-command ::= DROP INDEX [IF EXISTS] [database-name .] index-name

The DROP INDEX statement removes an index added with the CREATE INDEX statement. The index named is completely removed from the disk. The only way to recover the index is to reenter the appropriate CREATE INDEX command.

The DROP INDEX statement does not reduce the size of the database file in the default mode. Empty space in the database is retained for later INSERTs. To remove free space in the database, use the VACUUM command. If AUTOVACUUM mode is enabled for a database then space will be freed automatically by DROP INDEX.


Query Language Understood by SQLite: DROP TABLE

SQL As Understood By SQLite

DROP TABLE

sql-command ::= DROP TABLE [IF EXISTS] [database-name.] table-name

The DROP TABLE statement removes a table added with the CREATE TABLE statement. The name specified is the table name. It is completely removed from the database schema and the disk file. The table can not be recovered. All indices associated with the table are also deleted.

The DROP TABLE statement does not reduce the size of the database file in the default mode. Empty space in the database is retained for later INSERTs. To remove free space in the database, use the VACUUM command. If AUTOVACUUM mode is enabled for a database then space will be freed automatically by DROP TABLE.

The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.


Query Language Understood by SQLite: DROP TRIGGER

SQL As Understood By SQLite

DROP TRIGGER

sql-statement ::= DROP TRIGGER [database-name .] trigger-name

The DROP TRIGGER statement removes a trigger created by the CREATE TRIGGER statement. The trigger is deleted from the database schema. Note that triggers are automatically dropped when the associated table is dropped.


Query Language Understood by SQLite: DROP VIEW

SQL As Understood By SQLite

DROP VIEW

sql-command ::= DROP VIEW view-name

The DROP VIEW statement removes a view created by the CREATE VIEW statement. The name specified is the view name. It is removed from the database schema, but no actual data in the underlying base tables is modified.


Query Language Understood by SQLite: EXPLAIN

SQL As Understood By SQLite

EXPLAIN

sql-statement ::= EXPLAIN sql-statement

The EXPLAIN command modifier is a non-standard extension. The idea comes from a similar command found in PostgreSQL, but the operation is completely different.

If the EXPLAIN keyword appears before any other SQLite SQL command then instead of actually executing the command, the SQLite library will report back the sequence of virtual machine instructions it would have used to execute the command had the EXPLAIN keyword not been present. For additional information about virtual machine instructions see the architecture description or the documentation on available opcodes for the virtual machine.


Query Language Understood by SQLite: expression

SQL As Understood By SQLite

expression

expr ::= | expr binary-op expr | expr [NOT] like-op expr [ESCAPE expr] | unary-op expr | ( expr ) | column-name | table-name . column-name | database-name . table-name . column-name | literal-value | parameter | function-name ( expr-list | * ) | expr ISNULL | expr NOTNULL | expr [NOT] BETWEEN expr AND expr | expr [NOT] IN ( value-list ) | expr [NOT] IN ( select-statement ) | expr [NOT] IN [database-name .] table-name | [EXISTS] ( select-statement ) | CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END | CAST ( expr AS type )
like-op ::= | LIKE | GLOB | REGEXP

This section is different from the others. Most other sections of this document talks about a particular SQL command. This section does not talk about a standalone command but about "expressions" which are subcomponents of most other commands.

SQLite understands the following binary operators, in order from highest to lowest precedence:

||
*    /    %
+    -
<<   >>   &    |
<    <=   >    >=
=    ==   !=   <>   IN
AND
OR

Supported unary operators are these:

-    +    !    ~    NOT

Note that there are two variations of the equals and not equals operators. Equals can be either = or ==. The non-equals operator can be either != or <>. The || operator is "concatenate" - it joins together the two strings of its operands. The operator % outputs the remainder of its left operand modulo its right operand.

The result of any binary operator is a numeric value, except for the || concatenation operator which gives a string result.

A literal value is an integer number or a floating point number. Scientific notation is supported. The "." character is always used as the decimal point even if the locale setting specifies "," for this role - the use of "," for the decimal point would result in syntactic ambiguity. A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. For example:

X'53514697465'

A literal value can also be the token "NULL".

A parameter specifies a placeholder in the expression for a literal value that is filled in at runtime using the sqlite3_bind API. Parameters can take several forms:

?NNN | A question mark followed by a number NNN holds a spot for the NNN-th parameter. NNN must be between 1 and 999.
? | A question mark that is not followed by a number holds a spot for the next unused parameter.
:AAAA | A colon followed by an identifier name holds a spot for a named parameter with the name AAAA. Named parameters are also numbered. The number assigned is the next unused number. To avoid confusion, it is best to avoid mixing named and numbered parameters.
$AAAA | A dollar-sign followed by an identifier name also holds a spot for a named parameter with the name AAAA. The identifier name in this case can include one or more occurances of "::" and a suffix enclosed in "(...)" containing any text at all. This syntax is the form of a variable name in the Tcl programming language.

Parameters that are not assigned values using sqlite3_bind are treated as NULL.

The LIKE operator does a pattern matching comparison. The operand to the right contains the pattern, the left hand operand contains the string to match against the pattern. A percent symbol % in the pattern matches any sequence of zero or more characters in the string. An underscore _ in the pattern matches any single character in the string. Any other character matches itself or it's lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only understands upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.).

If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. The escape character followed by a percent symbol, underscore or itself matches a literal percent symbol, underscore or escape character in the string, respectively. The infix LIKE operator is implemented by calling the user function like(X,Y).

The LIKE operator is not case sensitive and will match upper case characters on one side against lower case characters on the other. (A bug: SQLite only understands upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.).

The infix LIKE operator is implemented by calling the user function like(X,Y). If an ESCAPE clause is present, it adds a third parameter to the function call. If the functionality of LIKE can be overridden by defining an alternative implementation of the like() SQL function.

The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator is implemented by calling the user function glob(X,Y) and can be modified by overriding that function.

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a user-defined function named "regexp" is defined at run-time, that function will be called in order to implement the REGEXP operator.

A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers: "ROWID", "OID", or "_ROWID_". These special identifiers all describe the unique random integer key (the "row key") associated with every row of every table. The special identifiers only refer to the row key if the CREATE TABLE statement does not define a real column with the same name. Row keys act like read-only columns. A row key can be used anywhere a regular column can be used, except that you cannot change the value of a row key in an UPDATE or INSERT statement. "SELECT * ..." does not return the row key.

SELECT statements can appear in expressions as either the right-hand operand of the IN operator, as a scalar quantity, or as the operand of an EXISTS operator. As a scalar quantity or the operand of an IN operator, the SELECT should have only a single column in its result. Compound SELECTs (connected with keywords like UNION or EXCEPT) are allowed. With the EXISTS operator, the columns in the result set of the SELECT are ignored and the expression returns TRUE if one or more rows exist and FALSE if the result set is empty. If no terms in the SELECT expression refer to value in the containing query, then the expression is evaluated once prior to any other processing and the result is reused as necessary. If the SELECT expression does contain variables from the outer query, then the SELECT is reevaluated every time it is needed.

When a SELECT is the right operand of the IN operator, the IN operator returns TRUE if the result of the left operand is any of the values generated by the select. The IN operator may be preceded by the NOT keyword to invert the sense of the test.

When a SELECT appears within an expression but is not the right operand of an IN operator, then the first row of the result of the SELECT becomes the value used in the expression. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the SELECT is NULL.

A CAST expression changes the datatype of the into the type specified by <type>. <type> can be any non-empty type name that is valid for the type in a column definition of a CREATE TABLE statement.

Both simple and aggregate functions are supported. A simple function can be used in any expression. Simple functions return a result immediately based on their inputs. Aggregate functions may only be used in a SELECT statement. Aggregate functions compute their result across all rows of the result set.

The functions shown below are available by default. Additional functions may be written in C and added to the database engine using the sqlite3_create_function() API.

abs(X) | Return the absolute value of argument X.
coalesce(X,Y,...) | Return a copy of the first non-NULL argument. If all arguments are NULL then NULL is returned. There must be at least 2 arguments.
glob(X,Y) | This function is used to implement the "X GLOB Y" syntax of SQLite. The sqlite3_create_function() interface can be used to override this function and thereby change the operation of the GLOB operator.
ifnull(X,Y) | Return a copy of the first non-NULL argument. If both arguments are NULL then NULL is returned. This behaves the same as coalesce() above.
last_insert_rowid() | Return the ROWID of the last row insert from this connection to the database. This is the same value that would be returned from the sqlite_last_insert_rowid() API function.
length(X) | Return the string length of X in characters. If SQLite is configured to support UTF-8, then the number of UTF-8 characters is returned, not the number of bytes.
like(X,Y [,Z]) | This function is used to implement the "X LIKE Y [ESCAPE Z]" syntax of SQL. If the optional ESCAPE clause is present, then the user-function is invoked with three arguments. Otherwise, it is invoked with two arguments only. The sqlite_create_function() interface can be used to override this function and thereby change the operation of the LIKE operator. When doing this, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.
lower(X) | Return a copy of string X will all characters converted to lower case. The C library tolower() routine is used for the conversion, which means that this function might not work correctly on UTF-8 characters.
max(X,Y,...) | Return the argument with the maximum value. Arguments may be strings in addition to numbers. The maximum value is determined by the usual sort order. Note that max() is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.
min(X,Y,...) | Return the argument with the minimum value. Arguments may be strings in addition to numbers. The minimum value is determined by the usual sort order. Note that min() is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.
nullif(X,Y) | Return the first argument if the arguments are different, otherwise return NULL.
quote(X) | This routine returns a string which is the value of its argument suitable for inclusion into another SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals. The current implementation of VACUUM uses this function. The function is also useful when writing triggers to implement undo/redo functionality.
random(*) | Return a random integer between -2147483648 and +2147483647.
round(X) round(X,Y) | Round off the number X to Y digits to the right of the decimal point. If the Y argument is omitted, 0 is assumed.
soundex(X) | Compute the soundex encoding of the string X. The string "?000" is returned if the argument is NULL. This function is omitted from SQLite by default. It is only available the -DSQLITE_SOUNDEX=1 compiler option is used when SQLite is built.
sqlite_version(*) | Return the version string for the SQLite library that is running. Example: "2.8.0"
substr(X,Y,Z) | Return a substring of input string X that begins with the Y-th character and which is Z characters long. The left-most character of X is number 1. If Y is negative the the first character of the substring is found by counting from the right rather than the left. If SQLite is configured to support UTF-8, then characters indices refer to actual UTF-8 characters, not bytes.
typeof(X) | Return the type of the expression X. The only return values are "null", "integer", "real", "text", and "blob". SQLite's type handling is explained in Datatypes in SQLite Version 3.
upper(X) | Return a copy of input string X converted to all upper-case letters. The implementation of this function uses the C library routine toupper() which means it may not work correctly on UTF-8 strings.

The aggregate functions shown below are available by default. Additional aggregate functions written in C may be added using the sqlite3_create_function() API.

In any aggregate function that takes a single argument, that argument can be preceeded by the keyword DISTINCT. In such cases, duplicate elements are filtered before being passed into the aggregate function. For example, the function "count(distinct X)" will return the number of distinct values of column X instead of the total number of non-null values in column X.

avg(X) Return the average value of all non-NULL X within a group. String and BLOB values that do not look like numbers are interpreted as 0. The result of avg() is always a floating point value even if all inputs are integers.

count(X)
count(*) 		The first form return a count of the number of times that X is not NULL in a group. The second form (with no argument) returns the total number of rows in the group.
max(X) | Return the maximum value of all values in the group. The usual sort order is used to determine the maximum.
min(X) | Return the minimum non-NULL value of all values in the group. The usual sort order is used to determine the minimum. NULL is only returned if all values in the group are NULL.
sum(X)
total(X) 		Return the numeric sum of all non-NULL values in the group. If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it and most other SQL database engines implement sum() that way so SQLite does it in the same way in order to be compatible. The non-standard total() function is provided as a convenient way to work around this design problem in the SQL language.

The result of total() is always a floating point value. The result of sum() is an integer value if all non-NULL inputs are integers. If any input to sum() is neither an integer or a NULL then sum() returns a floating point value which might be an approximation to the true sum.

Sum() will throw an "integer overflow" exception if all inputs are integers or NULL and an integer overflow occurs at any point during the computation. Total() never throws an exception.


Query Language Understood by SQLite: INSERT

SQL As Understood By SQLite

INSERT

sql-statement ::= INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES(value-list) |
INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement

The INSERT statement comes in two basic forms. The first form (with the "VALUES" keyword) creates a single new row in an existing table. If no column-list is specified then the number of values must be the same as the number of columns in the table. If a column-list is specified, then the number of values must match the number of specified columns. Columns of the table that do not appear in the column list are filled with the default value, or with NULL if not default value is specified.

The second form of the INSERT statement takes it data from a SELECT statement. The number of columns in the result of the SELECT must exactly match the number of columns in the table if no column list is specified, or it must match the number of columns name in the column list. A new entry is made in the table for every row of the SELECT result. The SELECT may be simple or compound. If the SELECT statement has an ORDER BY clause, the ORDER BY is ignored.

The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. See the section titled ON CONFLICT for additional information. For compatibility with MySQL, the parser allows the use of the single keyword REPLACE as an alias for "INSERT OR REPLACE".


Query Language Understood by SQLite: ON CONFLICT clause

SQL As Understood By SQLite

ON CONFLICT clause

conflict-clause ::= | ON CONFLICT conflict-algorithm
conflict-algorithm ::= | ROLLBACK | ABORT | FAIL | IGNORE | REPLACE

The ON CONFLICT clause is not a separate SQL command. It is a non-standard clause that can appear in many other SQL commands. It is given its own section in this document because it is not part of standard SQL and therefore might not be familiar.

The syntax for the ON CONFLICT clause is as shown above for the CREATE TABLE command. For the INSERT and UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", to make the syntax seem more natural. For example, instead of "INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE". The keywords change but the meaning of the clause is the same either way.

The ON CONFLICT clause specifies an algorithm used to resolve constraint conflicts. There are five choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default algorithm is ABORT. This is what they mean:

ROLLBACK

When a constraint violation occurs, an immediate ROLLBACK occurs, thus ending the current transaction, and the command aborts with a return code of SQLITE_CONSTRAINT. If no transaction is active (other than the implied transaction that is created on every command) then this algorithm works the same as ABORT.

ABORT

When a constraint violation occurs, the command backs out any prior changes it might have made and aborts with a return code of SQLITE_CONSTRAINT. But no ROLLBACK is executed so changes from prior commands within the same transaction are preserved. This is the default behavior.

FAIL

When a constraint violation occurs, the command aborts with a return code SQLITE_CONSTRAINT. But any changes to the database that the command made prior to encountering the constraint violation are preserved and are not backed out. For example, if an UPDATE statement encountered a constraint violation on the 100th row that it attempts to update, then the first 99 row changes are preserved but changes to rows 100 and beyond never occur.

IGNORE

When a constraint violation occurs, the one row that contains the constraint violation is not inserted or changed. But the command continues executing normally. Other rows before and after the row that contained the constraint violation continue to be inserted or updated normally. No error is returned.

REPLACE

When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row. Thus the insert or update always occurs. The command continues executing normally. No error is returned. If a NOT NULL constraint violation occurs, the NULL value is replaced by the default value for that column. If the column has no default value, then the ABORT algorithm is used. If a CHECK constraint violation occurs then the IGNORE algorithm is used.

When this conflict resolution strategy deletes rows in order to satisfy a constraint, it does not invoke delete triggers on those rows. This behavior might change in a future release.

The algorithm specified in the OR clause of a INSERT or UPDATE overrides any algorithm specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm is used.


Pragma statements supported by SQLite

The PRAGMA command is a special command used to modify the operation of the SQLite library or to query the library for internal (non-table) data. The PRAGMA command is issued using the same interface as other SQLite commands (e.g. SELECT, INSERT) but is different in the following important respects:

  • Specific pragma statements may be removed and others added in future releases of SQLite. Use with caution!
  • No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact.
  • Some pragmas take effect during the SQL compilation stage, not the execution stage. This means if using the C-language sqlite3_compile(), sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper interface), the pragma may be applied to the library during the sqlite3_compile() call.
  • The pragma command is unlikely to be compatible with any other SQL engine.

The available pragmas fall into four basic categories:

  • Pragmas used to query the schema of the current database.
  • Pragmas used to modify the operation of the SQLite library in some manner, or to query for the current mode of operation.
  • Pragmas used to query or modify the databases two version values, the schema-version and the user-version.
  • Pragmas used to debug the library and verify that database files are not corrupted.

PRAGMA command syntax

sql-statement ::= PRAGMA name [= value] |
PRAGMA function(arg)

The pragmas that take an integer value also accept symbolic names. The strings "on", "true", and "yes" are equivalent to 1. The strings "off", "false", and "no" are equivalent to 0. These strings are case- insensitive, and do not require quotes. An unrecognized string will be treated as 1, and will not generate an error. When the value is returned it is as an integer.

Pragmas to modify library operation

  • PRAGMA auto_vacuum;
    PRAGMA auto_vacuum =     0 | 1;

    Query or set the auto-vacuum flag in the database.

    Normally, when a transaction that deletes data from a database is committed, the database file remains the same size. Unused database file pages are marked as such and reused later on, when data is inserted into the database. In this mode the VACUUM command is used to reclaim unused space.

    When the auto-vacuum flag is set, the database file shrinks when a transaction that deletes data is committed (The VACUUM command is not useful in a database with the auto-vacuum flag set). To support this functionality the database stores extra information internally, resulting in slightly larger database files than would otherwise be possible.

    It is only possible to modify the value of the auto-vacuum flag before any tables have been created in the database. No error message is returned if an attempt to modify the auto-vacuum flag is made after one or more tables have been created.

  • PRAGMA cache_size;
    PRAGMA cache_size =     Number-of-pages;

    Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses about 1.5K of memory. The default cache size is 2000. If you are doing UPDATEs or DELETEs that change many rows of a database and you do not mind if SQLite uses more memory, you can increase the cache size for a possible speed improvement.

    When you change the cache size using the cache_size pragma, the change only endures for the current session. The cache size reverts to the default value when the database is closed and reopened. Use the default_cache_size pragma to check the cache size permanently.

  • PRAGMA case_sensitive_like;
    PRAGMA case_sensitive_like =     0 | 1;

    The default behavior of the LIKE operator is to ignore case for latin1 characters. Hence, by default 'a' LIKE 'A' is true. The case_sensitive_like pragma can be turned on to change this behavior. When case_sensitive_like is enabled, 'a' LIKE 'A' is false but 'a' LIKE 'a' is still true.

  • PRAGMA count_changes;
    PRAGMA count_changes =     0 | 1;

    Query or change the count-changes flag. Normally, when the count-changes flag is not set, INSERT, UPDATE and DELETE statements return no data. When count-changes is set, each of these commands returns a single row of data consisting of one integer value - the number of rows inserted, modified or deleted by the command. The returned change count does not include any insertions, modifications or deletions performed by triggers.

  • PRAGMA default_cache_size;
    PRAGMA default_cache_size =     Number-of-pages;

    Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses 1K on disk and about 1.5K in memory. This pragma works like the cache_size pragma with the additional feature that it changes the cache size persistently. With this pragma, you can set the cache size once and that setting is retained and reused every time you reopen the database.

  • PRAGMA default_synchronous;

    This pragma was available in version 2.8 but was removed in version 3.0. It is a dangerous pragma whose use is discouraged. To help dissuide users of version 2.8 from employing this pragma, the documentation will not tell you what it does.

  • PRAGMA empty_result_callbacks;
    PRAGMA empty_result_callbacks =     0 | 1;

    Query or change the empty-result-callbacks flag.

    The empty-result-callbacks flag affects the sqlite3_exec API only. Normally, when the empty-result-callbacks flag is cleared, the callback function supplied to the sqlite3_exec() call is not invoked for commands that return zero rows of data. When empty-result-callbacks is set in this situation, the callback function is invoked exactly once, with the third parameter set to 0 (NULL). This is to enable programs that use the sqlite3_exec() API to retrieve column-names even when a query returns no data.

  • PRAGMA encoding;
    PRAGMA encoding = "UTF-8";
    PRAGMA encoding = "UTF-16";
    PRAGMA encoding = "UTF-16le";
    PRAGMA encoding = "UTF-16be";

    In first form, if the main database has already been created, then this pragma returns the text encoding used by the main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16 encoding) or "UTF-16be" (big-endian UTF-16 encoding). If the main database has not already been created, then the value returned is the text encoding that will be used to create the main database, if it is created by this session.

    The second and subsequent forms of this pragma are only useful if the main database has not already been created. In this case the pragma sets the encoding that the main database will be created with if it is created by this session. The string "UTF-16" is interpreted as "UTF-16 encoding using native machine byte-ordering". If the second and subsequent forms are used after the database file has already been created, they have no effect and are silently ignored.

    Once an encoding has been set for a database, it cannot be changed.

    Databases created by the ATTACH command always use the same encoding as the main database.

  • PRAGMA full_column_names;
    PRAGMA full_column_names =     0 | 1;

    Query or change the full-column-names flag. This flag affects the way SQLite names columns of data returned by SELECT statements when the expression for the column is a table-column name or the wildcard "*". Normally, such result columns are named <table-name/alias><column-name> if the SELECT statement joins two or more tables together, or simply <column-name> if the SELECT statement queries a single table. When the full-column-names flag is set, such columns are always named <table-name/alias> <column-name> regardless of whether or not a join is performed.

    If both the short-column-names and full-column-names are set, then the behaviour associated with the full-column-names flag is exhibited.

  • PRAGMA fullfsync
    PRAGMA fullfsync =     0 | 1;

    Query or change the fullfsync flag. This flag affects determines whether or not the F_FULLFSYNC syncing method is used on systems that support it. The default value is off. As of this writing (2006-02-10) only Mac OS X supports F_FULLFSYNC.

  • PRAGMA page_size;
    PRAGMA page_size =     bytes;

    Query or set the page-size of the database. The page-size may only be set if the database has not yet been created. The page size must be a power of two greater than or equal to 512 and less than or equal to 8192. The upper limit may be modified by setting the value of macro SQLITE_MAX_PAGE_SIZE during compilation. The maximum upper bound is 32768.

  • PRAGMA read_uncommitted;
    PRAGMA read_uncommitted =     0 | 1;

    Query, set, or clear READ UNCOMMITTED isolation. The default isolation level for SQLite is SERIALIZABLE. Any process or thread can select READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except between connections that share a common page and schema cache. Cache sharing is enabled using the sqlite3_enable_shared_cache() API and is only available between connections running the same thread. Cache sharing is off by default.

  • PRAGMA short_column_names;
    PRAGMA short_column_names =     0 | 1;

    Query or change the short-column-names flag. This flag affects the way SQLite names columns of data returned by SELECT statements when the expression for the column is a table-column name or the wildcard "*". Normally, such result columns are named <table-name/alias>lt;column-name> if the SELECT statement joins two or more tables together, or simply <column-name> if the SELECT statement queries a single table. When the short-column-names flag is set, such columns are always named <column-name> regardless of whether or not a join is performed.

    If both the short-column-names and full-column-names are set, then the behaviour associated with the full-column-names flag is exhibited.

  • PRAGMA synchronous;
    PRAGMA synchronous = FULL;     (2)
    PRAGMA synchronous = NORMAL;     (1)
    PRAGMA synchronous = OFF;     (0)

    Query or change the setting of the "synchronous" flag. The first (query) form will return the setting as an integer. When synchronous is FULL (2), the SQLite database engine will pause at critical moments to make sure that data has actually been written to the disk surface before continuing. This ensures that if the operating system crashes or if there is a power failure, the database will be uncorrupted after rebooting. FULL synchronous is very safe, but it is also slow. When synchronous is NORMAL, the SQLite database engine will still pause at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode. But in practice, you are more likely to suffer a catastrophic disk failure or some other unrecoverable hardware fault. With synchronous OFF (0), SQLite continues without pausing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF.

    In SQLite version 2, the default value is NORMAL. For version 3, the default was changed to FULL.

  • PRAGMA temp_store;
    PRAGMA temp_store = DEFAULT;     (0)
    PRAGMA temp_store = FILE;     (1)
    PRAGMA temp_store = MEMORY;     (2)

    Query or change the setting of the "temp_store" parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro TEMP_STORE is used to determine where temporary tables and indices are stored. When temp_store is MEMORY (2) temporary tables and indices are kept in memory. When temp_store is FILE (1) temporary tables and indices are stored in a file. The temp_store_directory pragma can be used to specify the directory containing this file. FILE is specified. When the temp_store setting is changed, all existing temporary tables, indices, triggers, and views are immediately deleted.

    It is possible for the library compile-time C preprocessor symbol TEMP_STORE to override this pragma setting. The following table summarizes the interaction of the TEMP_STORE preprocessor macro and the temp_store pragma:

    TEMP_STORE PRAGMA
    temp_store     		Storage used for
    TEMP tables and indices
    0 | any | file
    1 | 0 | file
    1 | 1 | file
    1 | 2 | memory
    2 | 0 | memory
    2 | 1 | file
    2 | 2 | memory
    3 | any | memory

  • PRAGMA temp_store_directory;
    PRAGMA temp_store_directory = 'directory-name';

    Query or change the setting of the "temp_store_directory" - the directory where files used for storing temporary tables and indices are kept. This setting lasts for the duration of the current connection only and resets to its default value for each new connection opened.

    When the temp_store_directory setting is changed, all existing temporary tables, indices, triggers, and viewers are immediately deleted. In practice, temp_store_directory should be set immediately after the database is opened.

    The value directory-name should be enclosed in single quotes. To revert the directory to the default, set the directory-name to an empty string, e.g., PRAGMA temp_store_directory = ''. An error is raised if directory-name is not found or is not writable.

    The default directory for temporary files depends on the OS. For Unix/Linux/OSX, the default is the is the first writable directory found in the list of: /var/tmp, /usr/tmp, /tmp, and current-directory. For Windows NT, the default directory is determined by Windows, generally C:\Documents and Settings\user-name\Local Settings\Temp\. Temporary files created by SQLite are unlinked immediately after opening, so that the operating system can automatically delete the files when the SQLite process exits. Thus, temporary files are not normally visible through ls or dir commands.

Pragmas to query the database schema

  • PRAGMA database_list;

    For each open database, invoke the callback function once with information about that database. Arguments include the index and the name the database was attached with. The first row will be for the main database. The second row will be for the database used to store temporary tables.

  • PRAGMA foreign_key_list(table-name);

    For each foreign key that references a column in the argument table, invoke the callback function with information about that foreign key. The callback function will be invoked once for each column in each foreign key.

  • PRAGMA index_info(index-name);

    For each column that the named index references, invoke the callback function once with information about that column, including the column name, and the column number.

  • PRAGMA index_list(table-name);

    For each index on the named table, invoke the callback function once with information about that index. Arguments include the index name and a flag to indicate whether or not the index must be unique.

  • PRAGMA table_info(table-name);

    For each column in the named table, invoke the callback function once with information about that column, including the column name, data type, whether or not the column can be NULL, and the default value for the column.

Pragmas to query/modify version values

  • PRAGMA [database.]schema_version;
    PRAGMA [database.]schema_version =     integer ;
    PRAGMA [database.]user_version;
    PRAGMA [database.]user_version =     integer ;

    The pragmas schema_version and user_version are used to set or get the value of the schema-version and user-version, respectively. Both the schema-version and the user-version are 32-bit signed integers stored in the database header.

    The schema-version is usually only manipulated internally by SQLite. It is incremented by SQLite whenever the database schema is modified (by creating or dropping a table or index). The schema version is used by SQLite each time a query is executed to ensure that the internal cache of the schema used when compiling the SQL query matches the schema of the database against which the compiled query is actually executed. Subverting this mechanism by using "PRAGMA schema_version" to modify the schema-version is potentially dangerous and may lead to program crashes or database corruption. Use with caution!

    The user-version is not used internally by SQLite. It may be used by applications for any purpose.

Pragmas to debug the library

  • PRAGMA integrity_check;

    The command does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then a single string is returned which is a description of all problems. If everything is in order, "ok" is returned.

  • PRAGMA parser_trace = ON; (1)
    PRAGMA parser_trace = OFF;     (0)

    Turn tracing of the SQL parser inside of the SQLite library on and off. This is used for debugging. This only works if the library is compiled without the NDEBUG macro.

  • PRAGMA vdbe_trace = ON; (1)
    PRAGMA vdbe_trace = OFF;     (0)

    Turn tracing of the virtual database engine inside of the SQLite library on and off. This is used for debugging. See the VDBE documentation for more information.

  • PRAGMA vdbe_listing = ON; (1)
    PRAGMA vdbe_listing = OFF;     (0)

    Turn listings of virtual machine programs on and off. With listing is on, the entire content of a program is printed just prior to beginning execution. This is like automatically executing an EXPLAIN prior to each statement. The statement executes normally after the listing is printed. This is used for debugging. See the VDBE documentation for more information.


Query Language Understood by SQLite: REINDEX

SQL As Understood By SQLite

REINDEX

sql-statement ::= REINDEX collation name
sql-statement ::= REINDEX [database-name .] table/index-name

The REINDEX command is used to delete and recreate indices from scratch. This is useful when the definition of a collation sequence has changed.

In the first form, all indices in all attached databases that use the named collation sequence are recreated. In the second form, if [database-name.]table/index-name identifies a table, then all indices associated with the table are rebuilt. If an index is identified, then only this specific index is deleted and recreated.

If no database-name is specified and there exists both a table or index and a collation sequence of the specified name, then indices associated with the collation sequence only are reconstructed. This ambiguity may be dispelled by always specifying a database-name when reindexing a specific table or index.


Query Language Understood by SQLite: REPLACE

SQL As Understood By SQLite

REPLACE

sql-statement ::= REPLACE INTO [database-name .] table-name [( column-list )] VALUES ( value-list ) |
REPLACE INTO [database-name .] table-name [( column-list )] select-statement

The REPLACE command is an alias for the "INSERT OR REPLACE" variant of the INSERT command. This alias is provided for compatibility with MySQL. See the INSERT command documentation for additional information.


Query Language Understood by SQLite: SELECT

SQL As Understood By SQLite

SELECT

sql-statement ::= | SELECT [ALL | DISTINCT] result [FROM table-list] [WHERE expr] [GROUP BY expr-list] [HAVING expr] [compound-op select]* [ORDER BY sort-expr-list] [LIMIT integer [( OFFSET | , ) integer]]
result ::= | result-column [, result-column]*
result-column ::= | * | table-name . * | expr [ [AS] string ]
table-list ::= | table [join-op table join-args]*
table ::= | table-name [AS alias] | ( select ) [AS alias]
join-op ::= | , | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN
join-args ::= | [ON expr] [USING ( id-list )]
sort-expr-list ::= | expr [sort-order] [, expr [sort-order]]*
sort-order ::= | [ COLLATE collation-name ] [ ASC | DESC ]
compound_op ::= | UNION | UNION ALL | INTERSECT | EXCEPT

The SELECT statement is used to query the database. The result of a SELECT is zero or more rows of data where each row has a fixed number of columns. The number of columns in the result is specified by the expression list in between the SELECT and FROM keywords. Any arbitrary expression can be used as a result. If a result expression is * then all columns of all tables are substituted for that one expression. If the expression is the name of a table followed by .* then the result is all columns in that one table.

The DISTINCT keyword causes a subset of result rows to be returned, in which each result row is different. NULL values are not treated as distinct from each other. The default behavior is that all result rows be returned, which can be made explicit with the keyword ALL.

The query is executed against one or more tables specified after the FROM keyword. If multiple tables names are separated by commas, then the query is against the cross join of the various tables. The full SQL-92 join syntax can also be used to specify joins. A sub-query in parentheses may be substituted for any table name in the FROM clause. The entire FROM clause may be omitted, in which case the result is a single row consisting of the values of the expression list.

The WHERE clause can be used to limit the number of rows over which the query operates.

The GROUP BY clauses causes one or more rows of the result to be combined into a single row of output. This is especially useful when the result contains aggregate functions. The expressions in the GROUP BY clause do not have to be expressions that appear in the result. The HAVING clause is similar to WHERE except that HAVING applies after grouping has occurred. The HAVING expression may refer to values, even aggregate functions, that are not in the result.

The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY is a list of expressions that are used as the key for the sort. The expressions do not have to be part of the result for a simple SELECT, but in a compound SELECT each sort expression must exactly match one of the result columns. Each sort expression may be optionally followed by a COLLATE keyword and the name of a collating function used for ordering text and/or keywords ASC or DESC to specify the sort order.

The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradition is intentional - it maximizes compatibility with legacy SQL database systems.

A compound SELECT is formed from two or more simple SELECTs connected by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT. In a compound SELECT, all the constituent SELECTs must specify the same number of result columns. There may be only a single ORDER BY clause at the end of the compound SELECT. The UNION and UNION ALL operators combine the results of the SELECTs to the right and left into a single big table. The difference is that in UNION all result rows are distinct where in UNION ALL there may be duplicates. The INTERSECT operator takes the intersection of the results of the left and right SELECTs. EXCEPT takes the result of left SELECT after removing the results of the right SELECT. When three or more SELECTs are connected into a compound, they group from left to right.


Query Language Understood by SQLite: UPDATE

SQL As Understood By SQLite

UPDATE

sql-statement ::= | UPDATE [ OR conflict-algorithm ] [database-name .] table-name SET assignment [, assignment]* [WHERE expr]
assignment ::= | column-name = expr

The UPDATE statement is used to change the value of columns in selected rows of a table. Each assignment in an UPDATE specifies a column name to the left of the equals sign and an arbitrary expression to the right. The expressions may use the values of other columns. All expressions are evaluated before any assignments are made. A WHERE clause can be used to restrict which rows are updated.

The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. See the section titled ON CONFLICT for additional information.


Query Language Understood by SQLite: VACUUM

SQL As Understood By SQLite

VACUUM

sql-statement ::= VACUUM [index-or-table-name]

The VACUUM command is an SQLite extension modeled after a similar command found in PostgreSQL. If VACUUM is invoked with the name of a table or index then it is suppose to clean up the named table or index. In version 1.0 of SQLite, the VACUUM command would invoke gdbm_reorganize() to clean up the backend database file.

VACUUM became a no-op when the GDBM backend was removed from SQLITE in version 2.0.0. VACUUM was reimplemented in version 2.8.1. The index or table name argument is now ignored.

When an object (table, index, or trigger) is dropped from the database, it leaves behind empty space. This makes the database file larger than it needs to be, but can speed up inserts. In time inserts and deletes can leave the database file structure fragmented, which slows down disk access to the database contents. The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure. It is not possible to perform the same process on an attached database file.

This command will fail if there is an active transaction. This command has no effect on an in-memory database.

As of SQLite version 3.1, an alternative to using the VACUUM command is auto-vacuum mode, enabled using the auto_vacuum pragma.


Command Line Tool

SQLite: A command-line program to administer SQLite databases

The SQLite library includes a simple command-line utility named sqlite that allows the user to manually enter and execute SQL commands against an SQLite database. This document provides a brief introduction on how to use sqlite.

Getting Started

To start the sqlite program, just type "sqlite" followed by the name the file that holds the SQLite database. If the file does not exist, a new one is created automatically. The sqlite program will then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.

For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:

$ sqlite ex1
SQLite version 2.0.0
Enter ".help" for instructions
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>

You can terminate the sqlite program by typing your systems End-Of-File character (usually a Control-D) or the interrupt character (usually a Control-C).

Make sure you type a semicolon at the end of each SQL command! The sqlite looks for a semicolon to know when your SQL command is complete. If you omit the semicolon, sqlite will give you a continuation prompt and wait for you to enter more text to be added to the current SQL command. This feature allows you to enter SQL commands that span multiple lines. For example:

sqlite> CREATE TABLE tbl2 (
   ...>   f1 varchar(30) primary key,
   ...>   f2 text,
   ...>   f3 real
   ...> );
sqlite>

Aside: Querying the SQLITE_MASTER table

The database schema in an SQLite database is stored in a special table named "sqlite_master". You can execute "SELECT" statements against the special sqlite_master table just like any other table in an SQLite database. For example:

$ sqlite ex1
SQlite vresion 2.0.0
Enter ".help" for instructions
sqlite> select * from sqlite_master;
    type = table
    name = tbl1
tbl_name = tbl1
rootpage = 3
     sql = create table tbl1(one varchar(10), two smallint)
sqlite>

But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against the sqlite_master table. The sqlite_master table is updated automatically as you create or drop tables and indices from the database. You can not make manual changes to the sqlite_master table.

The schema for TEMPORARY tables is not stored in the "sqlite_master" table since TEMPORARY tables are not visible to applications other than the application that created the table. The schema for TEMPORARY tables is stored in another special table named "sqlite_temp_master". The "sqlite_temp_master" table is temporary itself.

Special commands to sqlite

Most of the time, sqlite just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot ("."), then that line is intercepted and interpreted by the sqlite program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements.

For a listing of the available dot commands, you can enter ".help" at any time. For example:

sqlite> .help
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in a text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line(s)", "column(s)",
                       "insert", "list", or "html"
.mode insert TABLE     Generate SQL insert statements for TABLE
.nullvalue STRING      Print STRING instead of nothing for NULL data
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode
sqlite>

Changing Output Formats

The sqlite program is able to show the results of a query in five different formats: "line", "column", "list", "html", and "insert". You can use the ".mode" dot command to switch between these output formats.

The default output mode is "list". In list mode, each record of a query result is written on one line of output and each column within that record is separated by a specific separator string. The default separator is a pipe symbol ("|"). List mode is especially useful when you are going to send the output of a query to another program (such as AWK) for additional processing.

sqlite> .mode list
sqlite> select * from tbl1;
hello|10
goodbye|20
sqlite>

You can use the ".separator" dot command to change the separator for list mode. For example, to change the separator to a comma and a space, you could do this:

sqlite> .separator ", "
sqlite> select * from tbl1;
hello, 10
goodbye, 20
sqlite>

In "line" mode, each column in a row of the database is shown on a line by itself. Each line consists of the column name, an equal sign and the column data. Successive records are separated by a blank line. Here is an example of line mode output:

sqlite> .mode line
sqlite> select * from tbl1;
one = hello
two = 10

one = goodbye
two = 20
sqlite>

In column mode, each record is shown on a separate line with the data aligned in columns. For example:

sqlite> .mode column
sqlite> select * from tbl1;
one         two
----------  ----------
hello       10
goodbye     20
sqlite>

By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the ".width" command. Like this:

sqlite> .width 12 6
sqlite> select * from tbl1;
one           two
------------  ------
hello         10
goodbye       20
sqlite>

The ".width" command in the example above sets the width of the first column to 12 and the width of the second column to 6. All other column widths were unaltered. You can gives as many arguments to ".width" as necessary to specify the widths of as many columns as are in your query results.

If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.

The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the examples above, the column labels are on. To turn them off you could do this:

sqlite> .header off
sqlite> select * from tbl1;
hello         10
goodbye       20
sqlite>

Another useful output mode is "insert". In insert mode, the output is formatted to look like SQL INSERT statements. You can use insert mode to generate text that can later be used to input data into a different database.

When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:

sqlite> .mode insert new_table
sqlite> select * from tbl1;
INSERT INTO 'new_table' VALUES('hello',10);
INSERT INTO 'new_table' VALUES('goodbye',20);
sqlite>

The last output mode is "html". In this mode, sqlite writes the results of the query as an XHTML table. The beginning <TABLE> and the ending </TABLE> are not written, but all of the intervening <TR>s, <TH>s, and <TD>s are. The html output mode is envisioned as being useful for CGI.

Writing results to a file

By default, sqlite sends query results to standard output. You can change this using the ".output" command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use ".output stdout" to begin writing to standard output again. For example:

sqlite> .mode list
sqlite> .separator |
sqlite> .output test_file_1.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
hello|10
goodbye|20
$

Querying the database schema

The sqlite program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.

For example, to see a list of the tables in the database, you can enter ".tables".

sqlite> .tables
tbl1
tbl2
sqlite>

The ".tables" command is the same as setting list mode then executing the following query:

SELECT name FROM sqlite_master WHERE type='table'
UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table'
ORDER BY name;

In fact, if you look at the source code to the sqlite program (found in the source tree in the file src/shell.c) you'll find exactly the above query.

The ".indices" command works in a similar way to list all of the indices for a particular table. The ".indices" command takes a single argument which is the name of the table for which the indices are desired. Last, but not least, is the ".schema" command. With no arguments, the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. If you give the name of a table to ".schema", it shows the original CREATE statement used to make that table and all if its indices. We have:

sqlite> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite> .schema tbl2
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite>

The ".schema" command accomplishes the same thing as setting list mode, then entering the following query:

SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta'
ORDER BY tbl_name, type DESC, name

Or, if you give an argument to ".schema" because you only want the schema for a single table, the query looks like this:

SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE '%s' AND type!='meta'
ORDER BY type DESC, name

The %s in the query above is replaced by the argument to ".schema", of course. Notice that the argument to the ".schema" command appears to the right of an SQL LIKE operator. So you can use wildcards in the name of the table. For example, to get the schema for all tables whose names contain the character string "abc" you could enter:

sqlite> .schema %abc%

Along these same lines, the ".table" command also accepts a pattern as its first argument. If you give an argument to the .table command, a "%" is both appended and prepended and a LIKE clause is added to the query. This allows you to list only those tables that match a particular pattern.

The ".databases" command shows a list of all databases open in the current connection. There will always be at least 2. The first one is "main", the original database opened. The second is "temp", the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second column is the filename of the external file.

sqlite> .databases

Converting An Entire Database To An ASCII Text File

Use the ".dump" command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite.

A good way to make an archival copy of a database is this:

$ echo '.dump' | sqlite ex1 | gzip -c >ex1.dump.gz

This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:

$ zcat ex1.dump.gz | sqlite ex2

The text format used is the same as used by PostgreSQL, so you can also use the .dump command to export an SQLite database into a PostgreSQL database. Like this:

$ createdb ex2
$ echo '.dump' | sqlite ex1 | psql ex2

You can almost (but not quite) go the other way and export a PostgreSQL database into SQLite using the pg_dump utility. Unfortunately, when pg_dump writes the database schema information, it uses some SQL syntax that SQLite does not understand. So you cannot pipe the output of pg_dump directly into sqlite. But if you can recreate the schema separately, you can use pg_dump with the -a option to list just the data of a PostgreSQL database and import that directly into SQLite.

$ sqlite ex3 <schema.sql
$ pg_dump -a ex2 | sqlite ex3

Other Dot Commands

The ".explain" dot command can be used to set the output mode to "column" and to set the column widths to values that are reasonable for looking at the output of an EXPLAIN command. The EXPLAIN command is an SQLite-specific SQL extension that is useful for debugging. If any regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and analyzed but is not executed. Instead, the sequence of virtual machine instructions that would have been used to execute the SQL command are returned like a query result. For example:

sqlite> .explain
sqlite> explain delete from tbl1 where two<20;
addr  opcode        p1     p2     p3
----  ------------  -----  -----  -------------------------------------
0     ListOpen      0      0
1     Open          0      1      tbl1
2     Next          0      9
3     Field         0      1
4     Integer       20     0
5     Ge            0      2
6     Key           0      0
7     ListWrite     0      0
8     Goto          0      2
9     Noop          0      0
10    ListRewind    0      0
11    ListRead      0      14
12    Delete        0      0
13    Goto          0      11
14    ListClose     0      0

The ".timeout" command sets the amount of time that the sqlite program will wait for locks to clear on files it is trying to access before returning an error. The default value of the timeout is zero so that an error is returned immediately if any needed database table or index is locked.

And finally, we mention the ".exit" command which causes the sqlite program to exit.

Using sqlite in a shell script

One way to use sqlite in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as an added convenience, sqlite allows a single SQL command to be entered on the command line as a second argument after the database name. When the sqlite program is launched with two arguments, the second argument is passed to the SQLite library for processing, the query results are printed on standard output in list mode, and the program exits. This mechanism is designed to make sqlite easy to use in conjunction with programs like "awk". For example:

$ sqlite ex1 'select * from tbl1' |
>  awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$

Ending shell commands

SQLite commands are normally terminated by a semicolon. In the shell you can also use the word "GO" (case-insensitive) or a backslash character "\" on a line by itself to end a command. These are used by SQL Server and Oracle, respectively. These won't work in sqlite_exec(), because the shell translates these into a semicolon before passing them to that function.

Compiling the sqlite program from sources

The sqlite program is built automatically when you compile the sqlite library. Just get a copy of the source tree, run "configure" and then "make".

This page last modified on 2004/05/31 15:06:30


Query Language Understood by SQLite: SQLite keywords

SQL As Understood By SQLite

SQLite keywords

The SQL standard specifies a huge number of keywords which may not be used as the names of tables, indices, columns, or databases. The list is so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object.

If you want to use a keyword as a name, you need to quote it. There are three ways of quoting keywords in SQLite:

'keyword' | A keyword in single quotes is interpreted as a literal string if it occurs in a context where a string literal is allowed, otherwise it is understood as an identifier.
"keyword" | A keyword in double-quotes is interpreted as an identifier if it matches a known identifier. Otherwise it is interpreted as a string literal.
[keyword] | A keyword enclosed in square brackets is always understood as an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.

Quoted keywords are unaesthetic. To help you avoid them, SQLite allows many keywords to be used unquoted as the names of databases, tables, indices, triggers, views, and/or columns. In the list of keywords that follows, those that can be used as identifiers are shown in an italic font. Keywords that must be quoted in order to be used as identifiers are shown in bold.

SQLite adds new keywords from time to time when it take on new features. So to prevent you code from being broken by future enhancements, you should normally quote any indentifier that is an English language word, even if you do not have to.

The following are the keywords currently recognized by SQLite:

ABORT
AFTER
ALL
ALTER
AND
AS
ASC
ATTACH
AUTOINCREMENT
BEFORE
BEGIN
BETWEEN
BY
CASCADE
CASE
CHECK
COLLATE
COMMIT
CONFLICT
CONSTRAINT
CREATE
CROSS
 CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATABASE
DEFAULT
DEFERRABLE
DEFERRED
DELETE
DESC
DETACH
DISTINCT
DROP
EACH
ELSE
END
ESCAPE
EXCEPT
EXCLUSIVE
EXPLAIN
FAIL
FOR
FOREIGN
 FROM
FULL
GLOB
GROUP
HAVING
IGNORE
IMMEDIATE
IN
INDEX
INITIALLY
INNER
INSERT
INSTEAD
INTERSECT
INTO
IS
ISNULL
JOIN
KEY
LEFT
LIKE
LIMIT
 MATCH
NATURAL
NOT
NOTNULL
NULL
OF
OFFSET
ON
OR
ORDER
OUTER
PRAGMA
PRIMARY
RAISE
REFERENCES
REINDEX
RENAME
REPLACE
RESTRICT
RIGHT
ROLLBACK
ROW
 SELECT
SET
STATEMENT
TABLE
TEMP
TEMPORARY
THEN
TO
TRANSACTION
TRIGGER
UNION
UNIQUE
UPDATE
USING
VACUUM
VALUES
VIEW
WHEN
WHERE

Special names

The following are not keywords in SQLite, but are used as names of system objects. They can be used as an identifier for a different type of object.

_ROWID_
MAIN
OID
ROWID
SQLITE_MASTER
SQLITE_SEQUENCE
SQLITE_TEMP_MASTER
TEMP
This page last modified on 2006/03/15 16:26:10


Datatypes In SQLite Version 3

Datatypes In SQLite Version 3

1. Storage Classes

Version 2 of SQLite stores all column values as ASCII text. Version 3 enhances this by providing the ability to store integer and real numbers in a more compact format and the capability to store BLOB data.

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

  • NULL. The value is a NULL value.

  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).

  • BLOB. The value is a blob of data, stored exactly as it was input.

As in SQLite version 2, any column in a version 3 database except an INTEGER PRIMARY KEY may be used to store any type of value. The exception to this rule is described below under 'Strict Affinity Mode'.

All values supplied to SQLite, whether as literals embedded in SQL statements or values bound to pre-compiled SQL statements are assigned a storage class before the SQL statement is executed. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.

Storage classes are initially assigned as follows:

  • Values specified as literals as part of SQL statements are assigned storage class TEXT if they are enclosed by single or double quotes, INTEGER if the literal is specified as an unquoted number with no decimal point or exponent, REAL if the literal is an unquoted number with a decimal point or exponent and NULL if the value is a NULL. Literals with storage class BLOB are specified using the X'ABCD' notation.

  • Values supplied using the sqlite3_bind_* APIs are assigned the storage class that most closely matches the native type bound (i.e. sqlite3_bind_blob() binds a value with storage class BLOB).

The storage class of a value that is the result of an SQL scalar operator depends on the outermost operator of the expression. User-defined functions may return values with any storage class. It is not generally possible to determine the storage class of the result of an expression at compile time.

2. Column Affinity

In SQLite version 3, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called manifest typing.) All other SQL databases engines that we are aware of use the more restrictive system of static typing where the type is associated with the container, not the value.

In order to maximize compatibility between SQLite and other database engines, SQLite support the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The key here is that the type is recommended, not required. Any column can still store any type of data, in theory. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".

Each column in an SQLite 3 database is assigned one of the following type affinities:

  • TEXT
  • NUMERIC
  • INTEGER
  • NONE

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted to text form before being stored.

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful, then the value is stored using the INTEGER or REAL storage class. If the conversion cannot be performed the value is stored using the TEXT storage class. No attempt is made to convert NULL or blob values.

A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no floating point component (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class.

A column with affinity NONE does not prefer one storage class over another. It makes no attempt to coerce data before it is inserted.

2.1 Determination Of Column Affinity

The type affinity of a column is determined by the declared type of the column, according to the following rules:

  1. If the datatype contains the string "INT" then it is assigned INTEGER affinity.

  2. If the datatype of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

  3. If the datatype for a column contains the string "BLOB" or if no datatype is specified then the column has affinity NONE.

  4. Otherwise, the affinity is NUMERIC.

If a table is created using a "CREATE TABLE <table> AS SELECT..." statement, then all columns have no datatype specified and they are given no affinity.

2.2 Column Affinity Example

CREATE TABLE t1(
    t  TEXT,
    nu NUMERIC,
    i  INTEGER,
    no BLOB
);

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, TEXT
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);

3. Comparison Expressions

Like SQLite version 2, version 3 features the binary comparison operators '=', '<', '<=', '>=' and '!=', an operation to test for set membership, 'IN', and the ternary comparison operator 'BETWEEN'.

The results of a comparison depend on the storage classes of the two values being compared, according to the following rules:

  • A value with storage class NULL is considered less than any other value (including another value with storage class NULL).

  • An INTEGER or REAL value is less than any TEXT or BLOB value. When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed.

  • A TEXT value is less than a BLOB value. When two TEXT values are compared, the C library function memcmp() is usually used to determine the result. However this can be overridden, as described under 'User-defined collation Sequences' below.

  • When two BLOB values are compared, the result is always determined using memcmp().

SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. For binary comparisons, this is done in the cases enumerated below. The term "expression" used in the bullet points below means any SQL scalar expression or literal other than a column value.

  • When a column value is compared to the result of an expression, the affinity of the column is applied to the result of the expression before the comparison takes place.

  • When two column values are compared, if one column has INTEGER or NUMERIC affinity and the other does not, the NUMERIC affinity is applied to any values with storage class TEXT extracted from the non-NUMERIC column.

  • When the results of two expressions are compared, the no conversions occur. The results are compared as is. If a string is compared to a number, the number will always be less than the string.

In SQLite, the expression "a BETWEEN b AND c" is equivalent to "a >= b AND a <= c", even if this means that different affinities are applied to 'a' in each of the comparisons required to evaluate the expression.

Expressions of the type "a IN (SELECT b ....)" are handled by the three rules enumerated above for binary comparisons (e.g. in a similar manner to "a = b"). For example if 'b' is a column value and 'a' is an expression, then the affinity of 'b' is applied to 'a' before any comparisons take place.

SQLite treats the expression "a IN (x, y, z)" as equivalent to "a = z OR a = y OR a = z".

3.1 Comparison Example

CREATE TABLE t1(
    a TEXT,
    b NUMERIC,
    c BLOB
);

-- Storage classes for the following row:
-- TEXT, REAL, TEXT
INSERT INTO t1 VALUES('500', '500', '500');

-- 60 and 40 are converted to '60' and '40' and values are compared as TEXT.
SELECT a < 60, a < 40 FROM t1;
1|0

-- Comparisons are numeric. No conversions are required.
SELECT b < 60, b < 600 FROM t1;
0|1

-- Both 60 and 600 (storage class NUMERIC) are less than '500'
-- (storage class TEXT).
SELECT c < 60, c < 600 FROM t1;
0|0

4. Operators

All mathematical operators (which is to say, all operators other than the concatenation operator "||") apply NUMERIC affinity to all operands prior to being carried out. If one or both operands cannot be converted to NUMERIC then the result of the operation is NULL.

For the concatenation operator, TEXT affinity is applied to both operands. If either operand cannot be converted to TEXT (because it is NULL or a BLOB) then the result of the concatenation is NULL.

5. Sorting, Grouping and Compound SELECTs

When values are sorted by an ORDER by clause, values with storage class NULL come first, followed by INTEGER and REAL values interspersed in numeric order, followed by TEXT values usually in memcmp() order, and finally BLOB values in memcmp() order. No storage class conversions occur before the sort.

When grouping values with the GROUP BY clause values with different storage classes are considered distinct, except for INTEGER and REAL values which are considered equal if they are numerically equal. No affinities are applied to any values as the result of a GROUP by clause.

The compound SELECT operators UNION, INTERSECT and EXCEPT perform implicit comparisons between values. Before these comparisons are performed an affinity may be applied to each value. The same affinity, if any, is applied to all values that may be returned in a single column of the compound SELECT result set. The affinity applied is the affinity of the column returned by the left most component SELECTs that has a column value (and not some other kind of expression) in that position. If for a given compound SELECT column none of the component SELECTs return a column value, no affinity is applied to the values from that column before they are compared.

6. Other Affinity Modes

The above sections describe the operation of the database engine in 'normal' affinity mode. SQLite version 3 will feature two other affinity modes, as follows:

  • Strict affinity mode. In this mode if a conversion between storage classes is ever required, the database engine returns an error and the current statement is rolled back.

  • No affinity mode. In this mode no conversions between storage classes are ever performed. Comparisons between values of different storage classes (except for INTEGER and REAL) are always false.

7. User-defined Collation Sequences

By default, when SQLite compares two text values, the result of the comparison is determined using memcmp(), regardless of the encoding of the string. SQLite v3 provides the ability for users to supply arbitrary comparison functions, known as user-defined collation sequences, to be used instead of memcmp().

Aside from the default collation sequence BINARY, implemented using memcmp(), SQLite features one extra built-in collation sequences intended for testing purposes, the NOCASE collation:

  • BINARY - Compares string data using memcmp(), regardless of text encoding.
  • NOCASE - The same as binary, except the 26 upper case characters used by the English language are folded to their lower case equivalents before the comparison is performed.

7.1 Assigning Collation Sequences from SQL

Each column of each table has a default collation type. If a collation type other than BINARY is required, a COLLATE clause is specified as part of the column definition to define it.

Whenever two text values are compared by SQLite, a collation sequence is used to determine the results of the comparison according to the following rules. Sections 3 and 5 of this document describe the circumstances under which such a comparison takes place.

For binary comparison operators (=, <, >, <= and >=) if either operand is a column, then the default collation type of the column determines the collation sequence to use for the comparison. If both operands are columns, then the collation type for the left operand determines the collation sequence used. If neither operand is a column, then the BINARY collation sequence is used.

The expression "x BETWEEN y and z" is equivalent to "x >= y AND x <= z". The expression "x IN (SELECT y ...)" is handled in the same way as the expression "x = y" for the purposes of determining the collation sequence to use. The collation sequence used for expressions of the form "x IN (y, z ...)" is the default collation type of x if x is a column, or BINARY otherwise.

An ORDER BY clause that is part of a SELECT statement may be assigned a collation sequence to be used for the sort operation explicitly. In this case the explicit collation sequence is always used. Otherwise, if the expression sorted by an ORDER BY clause is a column, then the default collation type of the column is used to determine sort order. If the expression is not a column, then the BINARY collation sequence is used.

7.2 Collation Sequences Example

The examples below identify the collation sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required, and no collation sequence used, in the case of numeric, blob or NULL values.

CREATE TABLE t1(
    a,                 -- default collation type BINARY
    b COLLATE BINARY,  -- default collation type BINARY
    c COLLATE REVERSE, -- default collation type REVERSE
    d COLLATE NOCASE   -- default collation type NOCASE
);

-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = b) FROM t1;

-- Text comparison is performed using the NOCASE collation sequence.
SELECT (d = a) FROM t1;

-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = d) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.
SELECT ('abc' = c) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.
SELECT (c = 'abc') FROM t1;

-- Grouping is performed using the NOCASE collation sequence (i.e. values
-- 'abc' and 'ABC' are placed in the same group).
SELECT count(*) GROUP BY d FROM t1;

-- Grouping is performed using the BINARY collation sequence.
SELECT count(*) GROUP BY (d || '') FROM t1;

-- Sorting is performed using the REVERSE collation sequence.
SELECT * FROM t1 ORDER BY c;

-- Sorting is performed using the BINARY collation sequence.
SELECT * FROM t1 ORDER BY (c || '');

-- Sorting is performed using the NOCASE collation sequence.
SELECT * FROM t1 ORDER BY c COLLATE NOCASE;
This page last modified on 2006/04/01 14:38:41


Distinctive Features Of SQLite

This page highlights some of the characteristics of SQLite that are unusual and which make SQLite different from many other SQL database engines.

Zero-Configuration

SQLite does not need to be "installed" before it is used. There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured. There is no need for an administrator to create a new database instance or assign access permissions to users. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot.

SQLite just works.

Other more familiar database engines run great once you get them going. But doing the initial installation and configuration can be intimidatingly complex.

Serverless

Most SQL database engines are implemented as a separate server process. Programs that want to access the database communicate with the server using some kind of interprocess communcation (typically TCP/IP) to send requests to the server and to receive back results. SQLite does not work this way. With SQLite, the process that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process.

There are advantages and disadvantages to being serverless. The main advantage is that there is no separate server process to install, setup, configure, initialize, manage, and troubleshoot. This is one reason why SQLite is a "zero-configuration" database engine. Programs that use SQLite require no administrative support for setting up the database engine before they are run. Any program that is able to access the disk is able to use an SQLite database.

On the other hand, a database engine that uses a server can provide better protection from bugs in the client application - stray pointers in a client cannot corrupt memory on the server. And because a server is a single persistent process, it is able control database access with more precision, allowing for finer grain locking and better concurrancy.

Most SQL database engines are client/server based. Of those that are serverless, SQLite is the only one that this author knows of that allows multiple applications to access the same database at the same time.

Single Database File

An SQLite database is a single ordinary disk file that can be located anywhere in the directory hierarchy. If SQLite can read the disk file then it can read anything in the database. If the disk file and its directory are writable, then SQLite can change anything in the database. Database files can easily be copied onto a USB memory stick or emailed for sharing.

Other SQL database engines tend to store data as a large collection of files. Often these files are in a standard location that only the database engine itself can access. This makes the data more secure, but also makes it harder to access. Some SQL database engines provide the option of writing directly to disk and bypassing the filesystem all together. This provides added performance, but at the cost of considerable setup and maintenance complexity.

Compact

When optimized for size, the whole SQLite library with everything enabled is less than 225KiB in size (as measured on an ix86 using the "size" utility from the GNU compiler suite.) Unneeded features can be disabled at compile-time to further reduce the size of the library to under 170KiB if desired.

Most other SQL database engines are much larger than this. IBM boasts that it's recently released CloudScape database engine is "only" a 2MiB jar file - 10 times larger than SQLite even after it is compressed! Firefox boasts that it's client-side library is only 350KiB. That's 50% larger than SQLite and does not even contain the database engine. The Berkeley DB library from Sleepycat is 450KiB and it omits SQL support, providing the programmer with only simple key/value pairs.

Manifest typing

Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.)

The SQL language specification calls for static typing. So some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The authors argue that static typing is a bug in the SQL specification that SQLite has fixed in a backwards compatible way.

Variable-length records

Most other SQL database engines allocated a fixed amount of disk space for each row in most tables. They play special tricks for handling BLOBs and CLOBs which can be of wildly varying length. But for most tables, if you declare a column to be a VARCHAR(100) then the database engine will allocate 100 bytes of disk space regardless of how much information you actually store in that column.

SQLite, in contrast, use only the amount of disk space actually needed to store the information in a row. If you store a single character in a VARCHAR(100) column, then only a single byte of disk space is consumed. (Actually two bytes - there is some overhead at the beginning of each column to record its datatype and length.)

The use of variable-length records by SQLite has a number of advantages. It results in smaller database files, obviously. It also makes the database run faster, since there is less information to move to and from disk. And, the use of variable-length records makes it possible for SQLite to employ manifest typing instead of static typing.

Readable source code

The source code to SQLite is designed to be readable and accessible to the average programmer. All procedures and data structures and many automatic variables are carefully commented with useful information about what they do. Boilerplate commenting is omitted.

SQL statements compile into virtual machine code

Every SQL database engine compiles each SQL statement into some kind of internal data structure which is then used to carry out the work of the statement. But in most SQL engines that internal data structure is a complex web of interlinked structures and objects. In SQLite, the compiled form of statements is a short program in a machine-language like representation. Users of the database can view this virtual machine language by prepending the EXPLAIN keyword to a query.

The use of a virtual machine in SQLite has been a great benefit to library's development. The virtual machine provides a crisp, well-defined junction between the front-end of SQLite (the part that parses SQL statements and generates virtual machine code) and the back-end (the part that executes the virtual machine code and computes a result.) The virtual machine allows the developers to see clearly and in an easily readable form what SQLite is trying to do with each statement it compiles, which is a tremendous help in debugging. Depending on how it is compiled, SQLite also has the capability of tracing the execution of the virtual machine - printing each virtual machine instruction and its result as it executes.

Public domain

The source code for SQLite is in the public domain. No claim of copyright is made on any part of the core source code. (The documentation and test code is a different matter - some sections of documentation and test logic are governed by open-sources licenses.) All contributors to the SQLite core software have signed affidavits specifically disavowing any copyright interest in the code. This means that anybody is able to legally do anything they want with the SQLite source code.

There are other SQL database engines with liberal licenses that allow the code to be broadly and freely used. But those other engines are still governed by copyright law. SQLite is different in that copyright law simply does not apply.

The source code files for other SQL database engines typically begin with a comment describing your license rights to view and copy that file. The SQLite source code contains no license since it is not governed by copyright. Instead of a license, the SQLite source code offers a blessing:

May you do good and not evil
May you find forgiveness for yourself and forgive others
May you share freely, never taking more than you give.

SQL language extensions

SQLite provides a number of enhancements to the SQL language not normally found in other database engines. The EXPLAIN keyword and manifest typing have already been mentioned above. SQLite also provides statements such as REPLACE and the ON CONFLICT clause that allow for added control over the resolution of constraint conflicts. SQLite supports ATTACH and DETACH commands that allow multiple independent databases to be used together in the same query. And SQLite defines APIs that allows the user to add new SQL functions and collating sequences.
This page last modified on 2005/09/08 11:04:57


NULL Handling in SQLite

NULL Handling in SQLite Versus Other Database Engines

The goal is to make SQLite handle NULLs in a standards-compliant way. But the descriptions in the SQL standards on how to handle NULLs seem ambiguous. It is not clear from the standards documents exactly how NULLs should be handled in all circumstances.

So instead of going by the standards documents, various popular SQL engines were tested to see how they handle NULLs. The idea was to make SQLite work like all the other engines. A SQL test script was developed and run by volunteers on various SQL RDBMSes and the results of those tests were used to deduce how each engine processed NULL values. The original tests were run in May of 2002. A copy of the test script is found at the end of this document.

SQLite was originally coded in such a way that the answer to all questions in the chart below would be "Yes". But the experiments run on other SQL engines showed that none of them worked this way. So SQLite was modified to work the same as Oracle, PostgreSQL, and DB2. This involved making NULLs indistinct for the purposes of the SELECT DISTINCT statement and for the UNION operator in a SELECT. NULLs are still distinct in a UNIQUE column. This seems somewhat arbitrary, but the desire to be compatible with other engines outweighted that objection.

It is possible to make SQLite treat NULLs as distinct for the purposes of the SELECT DISTINCT and UNION. To do so, one should change the value of the NULL_ALWAYS_DISTINCT #define in the sqliteInt.h source file and recompile.

Update 2003-07-13: Since this document was originally written some of the database engines tested have been updated and users have been kind enough to send in corrections to the chart below. The original data showed a wide variety of behaviors, but over time the range of behaviors has converged toward the PostgreSQL/Oracle model. The only significant difference is that Informix and MS-SQL both threat NULLs as indistinct in a UNIQUE column.

The fact that NULLs are distinct for UNIQUE columns but are indistinct for SELECT DISTINCT and UNION continues to be puzzling. It seems that NULLs should be either distinct everywhere or nowhere. And the SQL standards documents suggest that NULLs should be distinct everywhere. Yet as of this writing, no SQL engine tested treats NULLs as distinct in a SELECT DISTINCT statement or in a UNION.

The following table shows the results of the NULL handling experiments.

 | SQLite | PostgreSQL | Oracle | Informix | DB2 | MS-SQL | OCELOT
Adding anything to null gives null | Yes | Yes | Yes | Yes | Yes | Yes | Yes
Multiplying null by zero gives null | Yes | Yes | Yes | Yes | Yes | Yes | Yes
nulls are distinct in a UNIQUE column | Yes | Yes | Yes | No | (Note 4) | No | Yes
nulls are distinct in SELECT DISTINCT | No | No | No | No | No | No | No
nulls are distinct in a UNION | No | No | No | No | No | No | No
"CASE WHEN null THEN 1 ELSE 0 END" is 0? | Yes | Yes | Yes | Yes | Yes | Yes | Yes
"null OR true" is true | Yes | Yes | Yes | Yes | Yes | Yes | Yes
"not (null AND false)" is true | Yes | Yes | Yes | Yes | Yes | Yes | Yes
 | MySQL 3.23.41 | MySQL 4.0.16 | Firebird | SQL Anywhere | Borland Interbase
Adding anything to null gives null | Yes | Yes | Yes | Yes | Yes
Multiplying null by zero gives null | Yes | Yes | Yes | Yes | Yes
nulls are distinct in a UNIQUE column | Yes | Yes | Yes | (Note 4) | (Note 4)
nulls are distinct in SELECT DISTINCT | No | No | No (Note 1) | No | No
nulls are distinct in a UNION | (Note 3) | No | No (Note 1) | No | No
"CASE WHEN null THEN 1 ELSE 0 END" is 0? | Yes | Yes | Yes | Yes | (Note 5)
"null OR true" is true | Yes | Yes | Yes | Yes | Yes
"not (null AND false)" is true | No | Yes | Yes | Yes | Yes
Notes: | 1. | Older versions of firebird omits all NULLs from SELECT DISTINCT and from UNION.
 | 2. | Test data unavailable.
 | 3. | MySQL version 3.23.41 does not support UNION.
 | 4. | DB2, SQL Anywhere, and Borland Interbase do not allow NULLs in a UNIQUE column.
 | 5. | Borland Interbase does not support CASE expressions.

The following script was used to gather information for the table above.

-- I have about decided that SQL's treatment of NULLs is capricious and cannot be
-- deduced by logic.  It must be discovered by experiment.  To that end, I have
-- prepared the following script to test how various SQL databases deal with NULL.
-- My aim is to use the information gather from this script to make SQLite as much
-- like other databases as possible.
--
-- If you could please run this script in your database engine and mail the results
-- to me at drh@hwaci.com, that will be a big help.  Please be sure to identify the
-- database engine you use for this test.  Thanks.
--
-- If you have to change anything to get this script to run with your database
-- engine, please send your revised script together with your results.
--

-- Create a test table with data
create table t1(a int, b int, c int);
insert into t1 values(1,0,0);
insert into t1 values(2,0,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);
insert into t1 values(7,null,null);

-- Check to see what CASE does with NULLs in its test expressions
select a, case when b<>0 then 1 else 0 end from t1;
select a+10, case when not b<>0 then 1 else 0 end from t1;
select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;
select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;
select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
select a+60, case b when c then 1 else 0 end from t1;
select a+70, case c when b then 1 else 0 end from t1;

-- What happens when you multiple a NULL by zero?
select a+80, b*0 from t1;
select a+90, b*c from t1;

-- What happens to NULL for other operators?
select a+100, b+c from t1;

-- Test the treatment of aggregate operators
select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1;

-- Check the behavior of NULLs in WHERE clauses
select a+110 from t1 where b<10;
select a+120 from t1 where not b>10;
select a+130 from t1 where b<10 OR c=1;
select a+140 from t1 where b<10 AND c=1;
select a+150 from t1 where not (b<10 AND c=1);
select a+160 from t1 where not (c=1 AND b<10);

-- Check the behavior of NULLs in a DISTINCT query
select distinct b from t1;

-- Check the behavior of NULLs in a UNION query
select b from t1 union select b from t1;

-- Create a new table with a unique column.  Check to see if NULLs are considered
-- to be distinct.
create table t2(a int, b int unique);
insert into t2 values(1,1);
insert into t2 values(2,null);
insert into t2 values(3,null);
select * from t2;

drop table t1;
drop table t2;
This page last modified on 2004/10/10 17:24:55


File Locking And Concurrency In SQLite Version 3

1.0 File Locking And Concurrency In SQLite Version 3

Version 3 of SQLite introduces a more complex locking and journaling mechanism designed to improve concurrency and reduce the writer starvation problem. The new mechanism also allows atomic commits of transactions involving multiple database files. This document describes the new locking mechanism. The intended audience is programmers who want to understand and/or modify the pager code and reviewers working to verify the design of SQLite version 3.

2.0 Overview

Locking and concurrency control are handled by the the pager module. The pager module is responsible for making SQLite "ACID" (Atomic, Consistent, Isolated, and Durable). The pager module makes sure changes happen all at once, that either all changes occur or none of them do, that two or more processes do not try to access the database in incompatible ways at the same time, and that once changes have been written they persist until explicitly deleted. The pager also provides an memory cache of some of the contents of the disk file.

The pager is unconcerned with the details of B-Trees, text encodings, indices, and so forth. From the point of view of the pager the database consists of a single file of uniform-sized blocks. Each block is called a "page" and is usually 1024 bytes in size. The pages are numbered beginning with 1. So the first 1024 bytes of the database are called "page 1" and the second 1024 bytes are call "page 2" and so forth. All other encoding details are handled by higher layers of the library. The pager communicates with the operating system using one of several modules (Examples: os_unix.c, os_win.c) that provides a uniform abstraction for operating system services.

The pager module effectively controls access for separate threads, or separate processes, or both. Throughout this document whenever the word "process" is written you may substitute the word "thread" without changing the truth of the statement.

3.0 Locking

From the point of view of a single process, a database file can be in one of five locking states:

UNLOCKED | No locks are held on the database. The database may be neither read nor written. Any internally cached data is considered suspect and subject to verification against the database file before being used. Other processes can read or write the database as their own locking states permit. This is the default state.
SHARED | The database may be read but not written. Any number of processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active.
RESERVED | A RESERVED lock means that the process is planning on writing to the database file at some point in the future but that it is currently just reading from the file. Only a single RESERVED lock may be active at one time, though multiple SHARED locks can coexist with a single RESERVED lock. RESERVED differs from PENDING in that new SHARED locks can be acquired while there is a RESERVED lock.
PENDING | A PENDING lock means that the process holding the lock wants to write to the database as soon as possible and is just waiting on all current SHARED locks to clear so that it can get an EXCLUSIVE lock. No new SHARED locks are permitted against the database if a PENDING lock is active, though existing SHARED locks are allowed to continue.
EXCLUSIVE | An EXCLUSIVE lock is needed in order to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held.

The operating system interface layer understands and tracks all five locking states described above. The pager module only tracks four of the five locking states. A PENDING lock is always just a temporary stepping stone on the path to an EXCLUSIVE lock and so the pager module does not track PENDING locks.

4.0 The Rollback Journal

Any time a process wants to make a changes to a database file, it first records enough information in the rollback journal to restore the database file back to its initial condition. Thus, before altering any page of the database, the original contents of that page must be written into the journal. The journal also records the initial size of the database so that if the database file grows it can be truncated back to its original size on a rollback.

The rollback journal is a ordinary disk file that has the same name as the database file with the suffix "-journal" added.

If SQLite is working with multiple databases at the same time (using the ATTACH command) then each database has its own journal. But there is also a separate aggregate journal called the master journal. The master journal does not contain page data used for rolling back changes. Instead the master journal contains the names of the individual file journals for each of the ATTACHed databases. Each of the individual file journals also contain the name of the master journal. If there are no ATTACHed databases (or if none of the ATTACHed database is participating in the current transaction) no master journal is created and the normal rollback journal contains an empty string in the place normally reserved for recording the name of the master journal.

A individual file journal is said to be hot if it needs to be rolled back in order to restore the integrity of its database. A hot journal is created when a process is in the middle of a database update and a program or operating system crash or power failure prevents the update from completing. Hot journals are an exception condition. Hot journals exist to recover from crashes and power failures. If everything is working correctly (that is, if there are no crashes or power failures) you will never get a hot journal.

If no master journal is involved, then a journal is hot if it exists and its corresponding database file does not have a RESERVED lock. If a master journal is named in the file journal, then the file journal is hot if its master journal exists and there is no RESERVED lock on the corresponding database file. It is important to understand when a journal is hot so the preceding rules will be repeated in bullets:

  • A journal is hot if...
    • It exists, and
    • It's master journal exists or the master journal name is an empty string, and
    • There is no RESERVED lock on the corresponding database file.

4.1 Dealing with hot journals

Before reading from a a database file, SQLite always checks to see if that database file has a hot journal. If the file does have a hot journal, then the journal is rolled back before the file is read. In this way, we ensure that the database file is in a consistent state before it is read.

When a process wants to read from a database file, it followed the following sequence of steps:

  1. Open the database file and obtain a SHARED lock. If the SHARED lock cannot be obtained, fail immediately and return SQLITE_BUSY.
  2. Check to see if the database file has a hot journal. If the file does not have a hot journal, we are done. Return immediately. If there is a hot journal, that journal must be rolled back by the subsequent steps of this algorithm.
  3. Acquire a PENDING lock then an EXCLUSIVE lock on the database file. (Note: Do not acquire a RESERVED lock because that would make other processes think the journal was no longer hot.) If we fail to acquire these locks it means another process is already trying to do the rollback. In that case, drop all locks, close the database, and return SQLITE_BUSY.
  4. Read the journal file and roll back the changes.
  5. Wait for the rolled back changes to be written onto the surface of the disk. This protects the integrity of the database in case another power failure or crash occurs.
  6. Delete the journal file.
  7. Delete the master journal file if it is safe to do so. This step is optional. It is here only to prevent stale master journals from cluttering up the disk drive. See the discussion below for details.
  8. Drop the EXCLUSIVE and PENDING locks but retain the SHARED lock.

After the algorithm above completes successfully, it is safe to read from the database file. Once all reading has completed, the SHARED lock is dropped.

4.2 Deleting stale master journals

A stale master journal is a master journal that is no longer being used for anything. There is no requirement that stale master journals be deleted. The only reason for doing so is to free up disk space.

A master journal is stale if no individual file journals are pointing to it. To figure out if a master journal is stale, we first read the master journal to obtain the names of all of its file journals. Then we check each of those file journals. If any of the file journals named in the master journal exists and points back to the master journal, then the master journal is not stale. If all file journals are either missing or refer to other master journals or no master journal at all, then the master journal we are testing is stale and can be safely deleted.

5.0 Writing to a database file

To write to a database, a process must first acquire a SHARED lock as described above (possibly rolling back incomplete changes if there is a hot journal). After a SHARED lock is obtained, a RESERVED lock must be acquired. The RESERVED lock signals that the process intends to write to the database at some point in the future. Only one process at a time can hold a RESERVED lock. But other processes can continue to read the database while the RESERVED lock is held.

If the process that wants to write is unable to obtain a RESERVED lock, it must mean that another process already has a RESERVED lock. In that case, the write attempt fails and returns SQLITE_BUSY.

After obtaining a RESERVED lock, the process that wants to write creates a rollback journal. The header of the journal is initialized with the original size of the database file. Space in the journal header is also reserved for a master journal name, though the master journal name is initially empty.

Before making changes to any page of the database, the process writes the original content of that page into the rollback journal. Changes to pages are held in memory at first and are not written to the disk. The original database file remains unaltered, which means that other processes can continue to read the database.

Eventually, the writing process will want to update the database file, either because its memory cache has filled up or because it is ready to commit its changes. Before this happens, the writer must make sure no other process is reading the database and that the rollback journal data is safely on the disk surface so that it can be used to rollback incomplete changes in the event of a power failure. The steps are as follows:

  1. Make sure all rollback journal data has actually been written to the surface of the disk (and is not just being held in the operating system's or disk controllers cache) so that if a power failure occurs the data will still be there after power is restored.
  2. Obtain a PENDING lock and then an EXCLUSIVE lock on the database file. If other processes are still have SHARED locks, the writer might have to wait until those SHARED locks clear before it is able to obtain an EXCLUSIVE lock.
  3. Write all page modifications currently held in memory out to the original database disk file.

If the reason for writing to the database file is because the memory cache was full, then the writer will not commit right away. Instead, the writer might continue to make changes to other pages. Before subsequent changes are written to the database file, the rollback journal must be flushed to disk again. Note also that the EXCLUSIVE lock that the writer obtained in order to write to the database initially must be held until all changes are committed. That means that no other processes are able to access the database from the time the memory cache first spills to disk until the transaction commits.

When a writer is ready to commit its changes, it executes the following steps:

  4. Obtain an EXCLUSIVE lock on the database file and make sure all memory changes have been written to the database file using the algorithm of steps 1-3 above.
  5. Flush all database file changes to the disk. Wait for those changes to actually be written onto the disk surface.
  6. Delete the journal file. This is the instant when the changes are committed. Prior to deleting the journal file, if a power failure or crash occurs, the next process to open the database will see that it has a hot journal and will roll the changes back. After the journal is deleted, there will no longer be a hot journal and the changes will persist.
  7. Drop the EXCLUSIVE and PENDING locks from the database file.

As soon as PENDING lock is released from the database file, other processes can begin reading the database again. In the current implementation, the RESERVED lock is also released, but that is not essential. Future versions of SQLite might provide a "CHECKPOINT" SQL command that will commit all changes made so far within a transaction but retain the RESERVED lock so that additional changes can be made without given any other process an opportunity to write.

If a transaction involves multiple databases, then a more complex commit sequence is used, as follows:

  4. Make sure all individual database files have an EXCLUSIVE lock and a valid journal.
  5. Create a master-journal. The name of the master-journal is arbitrary. (The current implementation appends random suffixes to the name of the main database file until it finds a name that does not previously exist.) Fill the master journal with the names of all the individual journals and flush its contents to disk.
  6. Write the name of the master journal into all individual journals (in space set aside for that purpose in the headers of the individual journals) and flush the contents of the individual journals to disk and wait for those changes to reach the disk surface.
  7. Flush all database file changes to the disk. Wait for those changes to actually be written onto the disk surface.
  8. Delete the master journal file. This is the instant when the changes are committed. Prior to deleting the master journal file, if a power failure or crash occurs, the individual file journals will be considered hot and will be rolled back by the next process that attempts to read them. After the master journal has been deleted, the file journals will no longer be considered hot and the changes will persist.
  9. Delete all individual journal files.
  10. Drop the EXCLUSIVE and PENDING locks from all database files.

5.1 Writer starvation

In SQLite version 2, if many processes are reading from the database, it might be the case that there is never a time when there are no active readers. And if there is always at least one read lock on the database, no process would ever be able to make changes to the database because it would be impossible to acquire a write lock. This situation is called writer starvation.

SQLite version 3 seeks to avoid writer starvation through the use of the PENDING lock. The PENDING lock allows existing readers to continue but prevents new readers from connecting to the database. So when a process wants to write a busy database, it can set a PENDING lock which will prevent new readers from coming in. Assuming existing readers do eventually complete, all SHARED locks will eventually clear and the writer will be given a chance to make its changes.

6.0 How To Corrupt Your Database Files

The pager module is robust but it is not completely failsafe. It can be subverted. This section attempts to identify and explain the risks.

Clearly, a hardware or operating system fault that introduces incorrect data into the middle of the database file or journal will cause problems. Likewise, if a rogue process opens a database file or journal and writes malformed data into the middle of it, then the database will become corrupt. There is not much that can be done about these kinds of problems so they are given no further attention.

SQLite uses POSIX advisory locks to implement locking on Unix. On windows it uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite assumes that these system calls all work as advertised. If that is not the case, then database corruption can result. One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under windows. Your best defense is to not use SQLite for files on a network filesystem.

SQLite uses the fsync() system call to flush data to the disk under Unix and it uses the FlushFileBuffers() to do the same under windows. Once again, SQLite assumes that these operating system services function as advertised. But it has been reported that fsync() and FlushFileBuffers() do not always work correctly, especially with inexpensive IDE disks. Apparently some manufactures of IDE disks have defective controller chips that report that data has reached the disk surface when in fact the data is still in volatile cache memory in the disk drive electronics. There are also reports that windows sometimes chooses to ignore FlushFileBuffers() for unspecified reasons. The author cannot verify any of these reports. But if they are true, it means that database corruption is a possibility following an unexpected power loss. These are hardware and/or operating system bugs that SQLite is unable to defend against.

If a crash or power failure occurs and results in a hot journal but that journal is deleted, the next process to open the database will not know that it contains changes that need to be rolled back. The rollback will not occur and the database will be left in an inconsistent state. Rollback journals might be deleted for any number of reasons:

  • An administrator might be cleaning up after an OS crash or power failure, see the journal file, think it is junk, and delete it.
  • Someone (or some process) might rename the database file but fail to also rename its associated journal.
  • If the database file has aliases (hard or soft links) and the file is opened by a different alias than the one used to create the journal, then the journal will not be found. To avoid this problem, you should not create links to SQLite database files.
  • Filesystem corruption following a power failure might cause the journal to be renamed or deleted.

The last (fourth) bullet above merits additional comment. When SQLite creates a journal file on Unix, it opens the directory that contains that file and calls fsync() on the directory, in an effort to push the directory information to disk. But suppose some other process is adding or removing unrelated files to the directory that contains the database and journal at the the moment of a power failure. The supposedly unrelated actions of this other process might result in the journal file being dropped from the directory and moved into "lost+found". This is an unlikely scenario, but it could happen. The best defenses are to use a journaling filesystem or to keep the database and journal in a directory by themselves.

For a commit involving multiple databases and a master journal, if the various databases were on different disk volumes and a power failure occurs during the commit, then when the machine comes back up the disks might be remounted with different names. Or some disks might not be mounted at all. When this happens the individual file journals and the master journal might not be able to find each other. The worst outcome from this scenario is that the commit ceases to be atomic. Some databases might be rolled back and others might not. All databases will continue to be self-consistent. To defend against this problem, keep all databases on the same disk volume and/or remount disks using exactly the same names after a power failure.

7.0 Transaction Control At The SQL Level

The changes to locking and concurrency control in SQLite version 3 also introduce some subtle changes in the way transactions work at the SQL language level. By default, SQLite version 3 operates in autocommit mode. In autocommit mode, all changes to the database are committed as soon as all operations associated with the current database connection complete.

The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is optional) is used to take SQLite out of autocommit mode. Note that the BEGIN command does not acquire any locks on the database. After a BEGIN command, a SHARED lock will be acquired when the first SELECT statement is executed. A RESERVED lock will be acquired when the first INSERT, UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired until either the memory cache fills up and must be spilled to disk or until the transaction commits. In this way, the system delays blocking read access to the file file until the last possible moment.

The SQL command "COMMIT" does not actually commit the changes to disk. It just turns autocommit back on. Then, at the conclusion of the command, the regular autocommit logic takes over and causes the actual commit to disk to occur. The SQL command "ROLLBACK" also operates by turning autocommit back on, but it also sets a flag that tells the autocommit logic to rollback rather than commit.

If the SQL COMMIT command turns autocommit on and the autocommit logic then tries to commit change but fails because some other process is holding a SHARED lock, then autocommit is turned back off automatically. This allows the user to retry the COMMIT at a later time after the SHARED lock has had an opportunity to clear.

If multiple commands are being executed against the same SQLite database connection at the same time, the autocommit is deferred until the very last command completes. For example, if a SELECT statement is being executed, the execution of the command will pause as each row of the result is returned. During this pause other INSERT, UPDATE, or DELETE commands can be executed against other tables in the database. But none of these changes will commit until the original SELECT statement finishes.


SQL Features That SQLite Does Not Implement

SQL Features That SQLite Does Not Implement

Rather than try to list all the features of SQL92 that SQLite does support, it is much easier to list those that it does not. Unsupported features of SQL92 are shown below.

The order of this list gives some hint as to when a feature might be added to SQLite. Those features near the top of the list are likely to be added in the near future. There are no immediate plans to add features near the bottom of the list.

FOREIGN KEY constraints | FOREIGN KEY constraints are parsed but are not enforced.
Complete trigger support | There is some support for triggers but it is not complete. Missing subfeatures include FOR EACH STATEMENT triggers (currently all triggers must be FOR EACH ROW), INSTEAD OF triggers on tables (currently INSTEAD OF triggers are only allowed on views), and recursive triggers - triggers that trigger themselves.
Complete ALTER TABLE support | Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
Nested transactions | The current implementation only allows a single active transaction.
RIGHT and FULL OUTER JOIN | LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.
Writing to VIEWs | VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.
GRANT and REVOKE | Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine.

If you find other SQL92 features that SQLite does not support, please add them to the Wiki page at http://www.sqlite.org/cvstrac/wiki?p=Unsupported

This page last modified on 2005/11/03 00:41:18


SQLite Shared-Cache Mode

1.0 SQLite Shared-Cache Mode

Starting with version 3.3.0, SQLite includes a special "shared-cache" mode (disabled by default) intended for use in embedded servers. If shared-cache mode is enabled and a thread establishes multiple connections to the same database, the connections share a single data and schema cache. This can significantly reduce the quantity of memory and IO required by the system.

Using shared-cache mode imposes some extra restrictions on passing database handles between threads and changes the semantics of the locking model in some cases. These details are described in full by this document. A basic understanding of the normal SQLite locking model (see File Locking And Concurrency In SQLite Version 3 for details) is assumed.

2.0 Shared-Cache Locking Model

Externally, from the point of view of another process or thread, two or more database connections using a shared-cache appear as a single connection. The locking protocol used to arbitrate between multiple shared-caches or regular database users is described elsewhere.

Figure 1

Figure 1 depicts an example runtime configuration where three database connections have been established. Connection 1 is a normal SQLite database connection. Connections 2 and 3 share a cache (and so must have been established by the same process thread). The normal locking protocol is used to serialize database access between connection 1 and the shared cache. The internal protocol used to serialize (or not, see "Read-Uncommitted Isolation Mode" below) access to the shared-cache by connections 2 and 3 is described in the remainder of this section.

There are three levels to the shared-cache locking model, transaction level locking, table level locking and schema level locking. They are described in the following three sub-sections.

2.1 Transaction Level Locking

SQLite connections can open two kinds of transactions, read and write transactions. This is not done explicitly, a transaction is implicitly a read-transaction until it first writes to a database table, at which point it becomes a write-transaction.

At most one connection to a single shared cache may open a write transaction at any one time. This may co-exist with any number of read transactions.

2.2 Table Level Locking

When two or more connections use a shared-cache, locks are used to serialize concurrent access attempts on a per-table basis. Tables support two types of locks, "read-locks" and "write-locks". Locks are granted to connections - at any one time, each database connection has either a read-lock, write-lock or no lock on each database table.

At any one time, a single table may have any number of active read-locks or a single active write lock. To read data a table, a connection must first obtain a read-lock. To write to a table, a connection must obtain a write-lock on that table. If a required table lock cannot be obtained, the query fails and SQLITE_LOCKED is returned to the caller.

Once a connection obtains a table lock, it is not released until the current transaction (read or write) is concluded.

2.2.1 Read-Uncommitted Isolation Mode

The behaviour described above may be modified slightly by using the read_uncommitted pragma to change the isolation level from serialized (the default), to read-uncommitted.

A database connection in read-uncommitted mode does not attempt to obtain read-locks before reading from database tables as described above. This can lead to inconsistent query results if another database connection modifies a table while it is being read, but it also means that a read-transaction opened by a connection in read-uncommitted mode can neither block nor be blocked by any other connection.

Read-uncommitted mode has no effect on the locks required to write to database tables (i.e. read-uncommitted connections must still obtain write-locks and hence database writes may still block or be blocked). Also, read-uncommitted mode has no effect on the sqlite_master locks required by the rules enumerated below (see section "Schema (sqlite_master) Level Locking").

  /* Set the value of the read-uncommitted flag:
  **
  **   True  -> Set the connection to read-uncommitted mode.
  **   False -> Set the connectino to serialized (the default) mode.
  */
  PRAGMA read_uncommitted = <boolean>;

  /* Retrieve the current value of the read-uncommitted flag */
  PRAGMA read_uncommitted;

2.3 Schema (sqlite_master) Level Locking

The sqlite_master table supports shared-cache read and write locks in the same way as all other database tables (see description above). The following special rules also apply:

  • A connection must obtain a read-lock on sqlite_master before accessing any database tables or obtaining any other read or write locks.
  • Before executing a statement that modifies the database schema (i.e. a CREATE or DROP TABLE statement), a connection must obtain a write-lock on sqlite_master.
  • A connection may not compile an SQL statement if any other connection is holding a write-lock on the sqlite_master table of any attached database (including the default database, "main").

3.0 Thread Related Issues

When shared-cache mode is enabled, a database connection may only be used by the thread that called sqlite3_open() to create it. If another thread attempts to use the database connection, in most cases an SQLITE_MISUSE error is returned. However this is not guaranteed and programs should not depend on this behaviour, in some cases a segfault may result.

4.0 Enabling Shared-Cache Mode

Shared-cache mode is enabled on a thread-wide basis. Using the C interface, the following API can be used to enable or disable shared-cache mode for the calling thread:

int sqlite3_enable_shared_cache(int);

It is illegal to call sqlite3_enable_shared_cache() if one or more open database connections were opened by the calling thread. If the argument is non-zero, shared-cache mode is enabled. If the argument is zero, shared-cache mode is disabled. The return value is either SQLITE_OK (if the operation was successful), SQLITE_NOMEM (if a malloc() failed), or SQLITE_MISUSE (if the thread has open database connections).


The SQLite Query Optimizer Overview

The SQLite Query Optimizer Overview

This document provides a terse overview of how the query optimizer for SQLite works. This is not a tutorial. The reader is likely to need some prior knowledge of how database engines operate in order to fully understand this text.

1.0 WHERE clause analysis

The WHERE clause on a query is broken up into "terms" where each term is separated from the others by an AND operator.

All terms of the WHERE clause are analyzed to see if they can be satisfied using indices. Terms that cannot be satisfied through the use of indices become tests that are evaluated against each row of the relevant input tables. No tests are done for terms that are completely satisfied by indices. Sometimes one or more terms will provide hints to indices but still must be evaluated against each row of the input tables.

The analysis of a term might cause new "virtual" terms to be added to the WHERE clause. Virtual terms can be used with indices to restrict a search. But virtual terms never generate code that is tested against input rows.

To be usable by an index a term must be of one of the following forms:

  column = expression
  column > expression
  column >= expression
  column < expression
  column <= expression
  expression = column
  expression > column
  expression >= column
  expression < column
  expression <= column
  column IN (expression-list)
  column IN (subquery)

If an index is created using a statement like this:

  CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);

Then the index might be used if the initial columns of the index (columns a, b, and so forth) appear in WHERE clause terms. All index columns must be used with the = or IN operators except for the right-most column which can use inequalities. For the right-most column of an index that is used, there can be up to two inequalities that must sandwich the allowed values of the column between two extremes.

It is not necessary for every column of an index to appear in a WHERE clause term in order for that index to be used. But there can not be gaps in the columns of the index that are used. Thus for the example index above, if there is no WHERE clause term that constraints column c, then terms that constraint columns a and b can be used with the index but not terms that constraint columns d through z. Similarly, no index column will be used (for indexing purposes) that is to the right of a column that is constrained only by inequalities. For the index above and WHERE clause like this:

  ... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'

Only columns a, b, and c of the index would be usable. The d column would not be usable because it occurs to the right of c and c is constrained only by inequalities.

2.0 The BETWEEN optimization

If a term of the WHERE clause is of the following form:

  expr1 BETWEEN expr2 AND expr3

Then two virtual terms are added as follows:

  expr1 >= expr2 AND expr1 <= expr3

If both virtual terms end up being used as constraints on an index, then the original BETWEEN term is omitted and the corresponding test is not performed on input rows. Thus if the BETWEEN term ends up being used as an index constraint no tests are ever performed on that term. On the other hand, the virtual terms themselves never causes tests to be performed on input rows. Thus if the BETWEEN term is not used as an index constraint and instead must be used to test input rows, the expr1 expression is only evaluated once.

3.0 The OR optimization

If a term consists of multiple subterms containing a common column name and separated by OR, like this:

  column = expr1 OR column = expr2 OR column = expr3 OR ...

Then the term is rewritten as follows:

  column IN (expr1,expr2,expr3,expr4,...)

The rewritten term then might go on to constraint an index using the normal rules for IN operators. Note that column must be the same column in every OR-connected subterm, although the column can occur on either the left or the right side of the = operator.

4.0 The LIKE optimization

Terms that are composed of the LIKE or GLOB operator can sometimes be used to constrain indices. There are many conditions on this use:

  1. The left-hand side of the LIKE or GLOB operator must be the name of an indexed column.
  2. The right-hand side of the LIKE or GLOB must be a string literal that does not begin with a wildcard character.
  3. The ESCAPE clause cannot appear on the LIKE operator.
  4. The build-in functions used to implement LIKE and GLOB must not have been overloaded using the sqlite3_create_function() API.
  5. For the GLOB operator, the column must use the default BINARY collating sequence.
  6. For the LIKE operator, if case_sensitive_like mode is enabled then the column must use the default BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must use the built-in NOCASE collating sequence.

The LIKE operator has two modes that can be set by a pragma. The default mode is for LIKE comparisons to be insensitive to differences of case for latin1 characters. Thus, by default, the following expression is true:

  'a' LIKE 'A'

By turned on the case_sensitive_like pragma as follows:

  PRAGMA case_sensitive_like=ON;

Then the LIKE operator pays attention to case and the example above would evaluate to false. Note that case insensitivity only applies to latin1 characters - basically the upper and lower case letters of English in the lower 127 byte codes of ASCII. International character sets are case sensitive in SQLite unless a user-supplied collating sequence is used. But if you employ a user-supplied collating sequence, the LIKE optimization describe here will never be taken.

The LIKE operator is case insensitive by default because this is what the SQL standard requires. You can change the default behavior at compile time by using the -DSQLITE_CASE_SENSITIVE_LIKE command-line option to the compiler.

The LIKE optimization might occur if the column named on the left of the operator uses the BINARY collating sequence (which is the default) and case_sensitive_like is turned on. Or the optimization might occur if the column uses the built-in NOCASE collating sequence and the case_sensitive_like mode is off. These are the only two combinations under which LIKE operators will be optimized. If the column on the right-hand side of the LIKE operator uses any collating sequence other than the built-in BINARY and NOCASE collating sequences, then no optimizations will ever be attempted on the LIKE operator.

The GLOB operator is always case sensitive. The column on the left side of the GLOB operator must always use the built-in BINARY collating sequence or no attempt will be made to optimize that operator with indices.

The right-hand side of the GLOB or LIKE operator must be a literal string value that does not begin with a wildcard. If the right-hand side is a parameter that is bound to a string, then no optimization is attempted. If the right-hand side begins with a wildcard character then no optimization is attempted.

Suppose the initial sequence of non-wildcard characters on the right-hand side of the LIKE or GLOB operator is x. We are using a single character to denote this non-wildcard prefix but the reader should understand that the prefix can consist of more than 1 character. Let y the smallest string that is the same length as /x/ but which compares greater than x. For example, if x is hello then y would be hellp. The LIKE and GLOB optimizations consist of adding two virtual terms like this:

  column >= x AND column < y

Under most circumstances, the original LIKE or GLOB operator is still tested against each input row even if the virtual terms are used to constrain an index. This is because we do not know what additional constraints may be imposed by characters to the right of the x prefix. However, if there is only a single global wildcard to the right of x, then the original LIKE or GLOB test is disabled. In other words, if the pattern is like this:

  column LIKE x%
  column GLOB x*

Then the original LIKE or GLOB tests are disabled when the virtual terms constrain an index because in that case we know that all of the rows selected by the index will pass the LIKE or GLOB test.

5.0 Joins

The current implementation of SQLite uses only loop joins. That is to say, joins are implemented as nested loops.

The default order of the nested loops in a join is for the left-most table in the FROM clause to form the outer loop and the right-most table to form the inner loop. However, SQLite will nest the loops in a different order if doing so will help it to select better indices.

Inner joins can be freely reordered. However a left outer join is neither commutative nor associative and hence will not be reordered. Inner joins to the left and right of the outer join might be reordered if the optimizer thinks that is advantageous but the outer joins are always evaluated in the order in which they occur.

When selecting the order of tables in a join, SQLite uses a greedy algorithm that runs in polynomial time.

The ON and USING clauses of a join are converted into additional terms of the WHERE clause prior to WHERE clause analysis described above in paragraph 1.0. Thus with SQLite, there is no advantage to use the newer SQL92 join syntax over the older SQL89 comma-join syntax. They both end up accomplishing exactly the same thing.

Join reordering is automatic and usually works well enough that programmer do not have to think about it. But occasionally some hints from the programmer are needed. For a description of when hints might be necessary and how to provide those hints, see the QueryPlans page in the Wiki.

6.0 Choosing between multiple indices

Each table in the FROM clause of a query can use at most one index, and SQLite strives to use at least one index on each table. Sometimes, two or more indices might be candidates for use on a single table. For example:

  CREATE TABLE ex2(x,y,z);
  CREATE INDEX ex2i1 ON ex2(x);
  CREATE INDEX ex2i2 ON ex2(y);
  SELECT z FROM ex2 WHERE x=5 AND y=6;

For the SELECT statement above, the optimizer can use the ex2i1 index to lookup rows of ex2 that contain x=5 and then test each row against the y=6 term. Or it can use the ex2i2 index to lookup rows of ex2 that contain y=6 then test each of those rows against the x=5 term.

When faced with a choice of two or more indices, SQLite tries to estimate the total amount of work needed to perform the query using each option. It then selects the option that gives the least estimated work.

To help the optimizer get a more accurate estimate of the work involved in using various indices, the user may optional run the ANALYZE command. The ANALYZE command scans all indices of database where there might be a choice between two or more indices and gathers statistics on the selectiveness of those indices. The results of this scan are stored in the sqlite_stat1 table. The contents of the sqlite_stat1 table are not updated as the database changes so after making significant changes it might be prudent to rerun ANALYZE. The results of an ANALYZE command are only available to database connections that are opened after the ANALYZE command completes.

Once created, the sqlite_stat1 table cannot be dropped. But its content can be viewed, modified, or erased. Erasing the entire content of the sqlite_stat1 table has the effect of undoing the ANALYZE command. Changing the content of the sqlite_stat1 table can get the optimizer deeply confused and cause it to make silly index choices. Making updates to the sqlite_stat1 table (except by running ANALYZE) is not recommended.

Terms of the WHERE clause can be manually disqualified for use with indices by prepending a unary + operator to the column name. The unary + is a no-op and will not slow down the evaluation of the test specified by the term. But it will prevent the term from constraining an index. So, in the example above, if the query were rewritten as:

  SELECT z FROM ex2 WHERE +x=5 AND y=6;

The + operator on the x column would prevent that term from constraining an index. This would force the use of the ex2i2 index.

7.0 Avoidance of table lookups

When doing an indexed lookup of a row, the usual procedure is to do a binary search on the index to find the index entry, then extract the rowid from the index and use that rowid to do a binary search on the original table. Thus a typical indexed lookup involves two binary searches. If, however, all columns that were to be fetched from the table are already available in the index itself, SQLite will use the values contained in the index and will never look up the original table row. This saves one binary search for each row and can make many queries run twice as fast.

8.0 ORDER BY optimizations

SQLite attempts to use an index to satisfy the ORDER BY clause of a query when possible. When faced with the choice of using an index to satisfy WHERE clause constraints or satisfying an ORDER BY clause, SQLite does the same work analysis described in section 6.0 and chooses the index that it believes will result in the fastest answer.

9.0 Subquery flattening

When a subquery occurs in the FROM clause of a SELECT, the default behavior is to evaluate the subquery into a transient table, then run the outer SELECT against the transient table. This is problematic since the transient table will not have any indices and the outer query (which is likely a join) will be forced to do a full table scan on the transient table.

To overcome this problem, SQLite attempts to flatten subqueries in the FROM clause of a SELECT. This involves inserting the FROM clause of the subquery into the FROM clause of the outer query and rewriting expressions in the outer query that refer to the result set of the subquery. For example:

  SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5

Would be rewritten using query flattening as:

  SELECT x+y AS a FROM t1 WHERE z<100 AND a>5

There is a long list of conditions that must all be met in order for query flattening to occur.

  1. The subquery and the outer query do not both use aggregates.
  2. The subquery is not an aggregate or the outer query is not a join.
  3. The subquery is not the right operand of a left outer join, or the subquery is not itself a join.
  4. The subquery is not DISTINCT or the outer query is not a join.
  5. The subquery is not DISTINCT or the outer query does not use aggregates.
  6. The subquery does not use aggregates or the outer query is not DISTINCT.
  7. The subquery has a FROM clause.
  8. The subquery does not use LIMIT or the outer query is not a join.
  9. The subquery does not use LIMIT or the outer query does not use aggregates.
  10. The subquery does not use aggregates or the outer query does not use LIMIT.
  11. The subquery and the outer query do not both have ORDER BY clauses.
  12. The subquery is not the right term of a LEFT OUTER JOIN or the subquery has no WHERE clause.

The proof that query flattening may safely occur if all of the the above conditions are met is left as an exercise to the reader.

Query flattening is an important optimization when views are used as each use of a view is translated into a subquery.

10.0 The MIN/MAX optimization

Queries of the following forms will be optimized to run in logarithmic time assuming appropriate indices exist:

  SELECT MIN(x) FROM table;
  SELECT MAX(x) FROM table;

In order for these optimizations to occur, they must appear in exactly the form shown above - changing only the name of the table and column. It is not permissible to add a WHERE clause or do any arithmetic on the result. The result set must contain a single column. The column in the MIN or MAX function must be an indexed column.


SQLite changes

This page provides a high-level summary of changes to SQLite. For more detail, refer the the checkin logs generated by CVS at http://www.sqlite.org/cvstrac/timeline.

2006 April 5 (3.3.5)

  • CHECK constraints use conflict resolution algorithms correctly.
  • The SUM() function throws an error on integer overflow.
  • Choose the column names in a compound query from the left-most SELECT instead of the right-most.
  • The sqlite3_create_collation() function honors the SQLITE_UTF16_ALIGNED flag.
  • SQLITE_SECURE_DELETE compile-time option causes deletes to overwrite old data with zeros.
  • Detect integer overflow in abs().
  • The random() function provides 64 bits of randomness instead of only 32 bits.
  • Parser detects and reports automaton stack overflow.
  • Change the round() function to return REAL instead of TEXT.
  • Allow WHERE clause terms on the left table of a LEFT OUTER JOIN to contain aggregate subqueries.
  • Skip over leading spaces in text to numeric conversions.
  • Various minor bug and documentation typo fixes and performance enhancements.
2006 February 11 (3.3.4)

  • Fix a blunder in the Unix mutex implementation that can lead to deadlock on multithreaded systems.
  • Fix an alignment problem on 64-bit machines
  • Added the fullfsync pragma.
  • Fix an optimizer bug that could have caused some unusual LEFT OUTER JOINs to give incorrect results.
  • The SUM function detects integer overflow and converts to accumulating an approximate result using floating point numbers
  • Host parameter names can begin with '@' for compatibility with SQL Server.
  • Other miscellaneous bug fixes
2006 January 31 (3.3.3)

  • Removed support for an ON CONFLICT clause on CREATE INDEX - it never worked correctly so this should not present any backward compatibility problems.
  • Authorizer callback now notified of ALTER TABLE ADD COLUMN commands
  • After any changes to the TEMP database schema, all prepared statements are invalidated and must be recreated using a new call to sqlite3_prepare()
  • Other minor bug fixes in preparation for the first stable release of version 3.3

2006 January 24 (3.3.2 beta)

  • Bug fixes and speed improvements. Improved test coverage.
  • Changes to the OS-layer interface: mutexes must now be recursive.
  • Discontinue the use of thread-specific data for out-of-memory exception handling

2006 January 16 (3.3.1 alpha)

  • Countless bug fixes
  • Speed improvements
  • Database connections can now be used by multiple threads, not just the thread in which they were created.

2006 January 10 (3.3.0 alpha)

  • CHECK constraints
  • IF EXISTS and IF NOT EXISTS clauses on CREATE/DROP TABLE/INDEX.
  • DESC indices
  • More efficient encoding of boolean values resulting in smaller database files
  • More aggressive SQLITE_OMIT_FLOATING_POINT
  • Separate INTEGER and REAL affinity
  • Added a virtual function layer for the OS interface
  • "exists" method added to the TCL interface
  • Improved response to out-of-memory errors
  • Database cache can be optionally shared between connections in the same thread
  • Optional READ UNCOMMITTED isolation (instead of the default isolation level of SERIALIZABLE) and table level locking when database connections share a common cache.
2005 December 19 (3.2.8)

  • Fix an obscure bug that can cause database corruption under the following unusual circumstances: A large INSERT or UPDATE statement which is part of an even larger transaction fails due to a uniqueness contraint but the containing transaction commits.
2005 December 19 (2.8.17)

  • Fix an obscure bug that can cause database corruption under the following unusual circumstances: A large INSERT or UPDATE statement which is part of an even larger transaction fails due to a uniqueness contraint but the containing transaction commits.
2005 September 24 (3.2.7)

  • GROUP BY now considers NULLs to be equal again, as it should
  • Now compiles on Solaris and OpenBSD and other Unix variants that lack the fdatasync() function
  • Now compiles on MSVC++6 again
  • Fix uninitialized variables causing malfunctions for various obscure queries
  • Correctly compute a LEFT OUTER JOINs that is constrained on the left table only
2005 September 17 (3.2.6)

  • Fix a bug that can cause database corruption if a VACUUM (or autovacuum) fails and is rolled back on a database that is larger than 1GiB
  • LIKE optiization now works for columns with COLLATE NOCASE
  • ORDER BY and GROUP BY now use bounded memory
  • Added support for COUNT(DISTINCT expr)
  • Change the way SUM() handles NULL values in order to comply with the SQL standard
  • Use fdatasync() instead of fsync() where possible in order to speed up commits slightly
  • Use of the CROSS keyword in a join turns off the table reordering optimization
  • Added the experimental and undocumented EXPLAIN QUERY PLAN capability
  • Use the unicode API in windows
2005 August 27 (3.2.5)

  • Fix a bug effecting DELETE and UPDATE statements that changed more than 40960 rows.
  • Change the makefile so that it no longer requires GNUmake extensions
  • Fix the --enable-threadsafe option on the configure script
  • Fix a code generator bug that occurs when the left-hand side of an IN operator is constant and the right-hand side is a SELECT statement
  • The PRAGMA synchronous=off statement now disables syncing of the master journal file in addition to the normal rollback journals
2005 August 24 (3.2.4)

  • Fix a bug introduced in the previous release that can cause a segfault while generating code for complex WHERE clauses.
  • Allow floating point literals to begin or end with a decimal point.
2005 August 21 (3.2.3)

  • Added support for the CAST operator
  • Tcl interface allows BLOB values to be transferred to user-defined functions
  • Added the "transaction" method to the Tcl interface
  • Allow the DEFAULT value of a column to call functions that have constant operands
  • Added the ANALYZE command for gathering statistics on indices and using those statistics when picking an index in the optimizer
  • Remove the limit (formerly 100) on the number of terms in the WHERE clause
  • The right-hand side of the IN operator can now be a list of expressions instead of just a list of constants
  • Rework the optimizer so that it is able to make better use of indices
  • The order of tables in a join is adjusted automatically to make better use of indices
  • The IN operator is now a candidate for optimization even if the left-hand side is not the left-most term of the index. Multiple IN operators can be used with the same index.
  • WHERE clause expressions using BETWEEN and OR are now candidates for optimization
  • Added the "case_sensitive_like" pragma and the SQLITE_CASE_SENSITIVE_LIKE compile-time option to set its default value to "on".
  • Use indices to help with GLOB expressions and LIKE expressions too when the case_sensitive_like pragma is enabled
  • Added support for grave-accent quoting for compatibility with MySQL
  • Improved test coverage
  • Dozens of minor bug fixes
2005 June 13 (3.2.2)

  • Added the sqlite3_db_handle() API
  • Added the sqlite3_get_autocommit() API
  • Added a REGEXP operator to the parser. There is no function to back up this operator in the standard build but users can add their own using sqlite3_create_function()
  • Speed improvements and library footprint reductions.
  • Fix byte alignment problems on 64-bit architectures.
  • Many, many minor bug fixes and documentation updates.
2005 March 29 (3.2.1)

  • Fix a memory allocation error in the new ADD COLUMN comment.
  • Documentation updates
2005 March 21 (3.2.0)

  • Added support for ALTER TABLE ADD COLUMN.
  • Added support for the "T" separator in ISO-8601 date/time strings.
  • Improved support for Cygwin.
  • Numerous bug fixes and documentation updates.
2005 March 16 (3.1.6)

  • Fix a bug that could cause database corruption when inserting record into tables with around 125 columns.
  • sqlite3_step() is now much more likely to invoke the busy handler and less likely to return SQLITE_BUSY.
  • Fix memory leaks that used to occur after a malloc() failure.
2005 March 11 (3.1.5)

  • The ioctl on OS-X to control syncing to disk is F_FULLFSYNC, not F_FULLSYNC. The previous release had it wrong.
2005 March 10 (3.1.4)

  • Fix a bug in autovacuum that could cause database corruption if a CREATE UNIQUE INDEX fails because of a constraint violation. This problem only occurs if the new autovacuum feature introduced in version 3.1 is turned on.
  • The F_FULLSYNC ioctl (currently only supported on OS-X) is disabled if the synchronous pragma is set to something other than "full".
  • Add additional forward compatibility to the future version 3.2 database file format.
  • Fix a bug in WHERE clauses of the form (rowid<'2')
  • New SQLITE_OMIT_... compile-time options added
  • Updates to the man page
  • Remove the use of strcasecmp() from the shell
  • Windows DLL exports symbols Tclsqlite_Init and Sqlite_Init
2005 February 19 (3.1.3)

  • Fix a problem with VACUUM on databases from which tables containing AUTOINCREMENT have been dropped.
  • Add forward compatibility to the future version 3.2 database file format.
  • Documentation updates
2005 February 15 (3.1.2)

  • Fix a bug that can lead to database corruption if there are two open connections to the same database and one connection does a VACUUM and the second makes some change to the database.
  • Allow "?" parameters in the LIMIT clause.
  • Fix VACUUM so that it works with AUTOINCREMENT.
  • Fix a race condition in AUTOVACUUM that can lead to corrupt databases
  • Add a numeric version number to the sqlite3.h include file.
  • Other minor bug fixes and performance enhancements.
2005 February 15 (2.8.16)

  • Fix a bug that can lead to database corruption if there are two open connections to the same database and one connection does a VACUUM and the second makes some change to the database.
  • Correctly handle quoted names in CREATE INDEX statements.
  • Fix a naming conflict between sqlite.h and sqlite3.h.
  • Avoid excess heap usage when copying expressions.
  • Other minor bug fixes.

2005 February 1 (3.1.1 BETA)

  • Automatic caching of prepared statements in the TCL interface
  • ATTACH and DETACH as well as some other operations cause existing prepared statements to expire.
  • Numerious minor bug fixes

2005 January 21 (3.1.0 ALPHA)

  • Autovacuum support added
  • CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP added
  • Support for the EXISTS clause added.
  • Support for correlated subqueries added.
  • Added the ESCAPE clause on the LIKE operator.
  • Support for ALTER TABLE ... RENAME TABLE ... added
  • AUTOINCREMENT keyword supported on INTEGER PRIMARY KEY
  • Many SQLITE_OMIT_ macros inserts to omit features at compile-time and reduce the library footprint.
  • The REINDEX command was added.
  • The engine no longer consults the main table if it can get all the information it needs from an index.
  • Many nuisance bugs fixed.
2004 October 11 (3.0.8)

  • Add support for DEFERRED, IMMEDIATE, and EXCLUSIVE transactions.
  • Allow new user-defined functions to be created when there are already one or more precompiled SQL statements.
  • Fix portability problems for Mingw/MSYS.
  • Fix a byte alignment problem on 64-bit Sparc machines.
  • Fix the ".import" command of the shell so that it ignores \r characters at the end of lines.
  • The "csv" mode option in the shell puts strings inside double-quotes.
  • Fix typos in documentation.
  • Convert array constants in the code to have type "const".
  • Numerous code optimizations, specially optimizations designed to make the code footprint smaller.
2004 September 18 (3.0.7)

  • The BTree module allocates large buffers using malloc() instead of off of the stack, in order to play better on machines with limited stack space.
  • Fixed naming conflicts so that versions 2.8 and 3.0 can be linked and used together in the same ANSI-C source file.
  • New interface: sqlite3_bind_parameter_index()
  • Add support for wildcard parameters of the form: "?nnn"
  • Fix problems found on 64-bit systems.
  • Removed encode.c file (containing unused routines) from the version 3.0 source tree.
  • The sqlite3_trace() callbacks occur before each statement is executed, not when the statement is compiled.
  • Makefile updates and miscellaneous bug fixes.

2004 September 02 (3.0.6 beta)

  • Better detection and handling of corrupt database files.
  • The sqlite3_step() interface returns SQLITE_BUSY if it is unable to commit a change because of a lock
  • Combine the implementations of LIKE and GLOB into a single pattern-matching subroutine.
  • Miscellaneous code size optimizations and bug fixes

2004 August 29 (3.0.5 beta)

  • Support for ":AAA" style bind parameter names.
  • Added the new sqlite3_bind_parameter_name() interface.
  • Support for TCL variable names embedded in SQL statements in the TCL bindings.
  • The TCL bindings transfer data without necessarily doing a conversion to a string.
  • The database for TEMP tables is not created until it is needed.
  • Add the ability to specify an alternative temporary file directory using the "sqlite_temp_directory" global variable.
  • A compile-time option (SQLITE_BUSY_RESERVED_LOCK) causes the busy handler to be called when there is contention for a RESERVED lock.
  • Various bug fixes and optimizations

2004 August 8 (3.0.4 beta)

  • CREATE TABLE and DROP TABLE now work correctly as prepared statements.
  • Fix a bug in VACUUM and UNIQUE indices.
  • Add the ".import" command to the command-line shell.
  • Fix a bug that could cause index corruption when an attempt to delete rows of a table is blocked by a pending query.
  • Library size optimizations.
  • Other minor bug fixes.
2004 July 22 (2.8.15)

  • This is a maintenance release only. Various minor bugs have been fixed and some portability enhancements are added.

2004 July 22 (3.0.3 beta)

  • The second beta release for SQLite 3.0.
  • Add support for "PRAGMA page_size" to adjust the page size of the database.
  • Various bug fixes and documentation updates.

2004 June 30 (3.0.2 beta)

  • The first beta release for SQLite 3.0.

2004 June 22 (3.0.1 alpha)

  • *** Alpha Release - Research And Testing Use Only ***
  • Lots of bug fixes.

2004 June 18 (3.0.0 alpha)

  • *** Alpha Release - Research And Testing Use Only ***
  • Support for internationalization including UTF-8, UTF-16, and user defined collating sequences.
  • New file format that is 25% to 35% smaller for typical use.
  • Improved concurrency.
  • Atomic commits for ATTACHed databases.
  • Remove cruft from the APIs.
  • BLOB support.
  • 64-bit rowids.
  • More information.

2004 June 9 (2.8.14)

  • Fix the min() and max() optimizer so that it works when the FROM clause consists of a subquery.
  • Ignore extra whitespace at the end of of "." commands in the shell.
  • Bundle sqlite_encode_binary() and sqlite_decode_binary() with the library.
  • The TEMP_STORE and DEFAULT_TEMP_STORE pragmas now work.
  • Code changes to compile cleanly using OpenWatcom.
  • Fix VDBE stack overflow problems with INSTEAD OF triggers and NULLs in IN operators.
  • Add the global variable sqlite_temp_directory which if set defines the directory in which temporary files are stored.
  • sqlite_interrupt() plays well with VACUUM.
  • Other minor bug fixes.
2004 March 8 (2.8.13)

  • Refactor parts of the code in order to make the code footprint smaller. The code is now also a little bit faster.
  • sqlite_exec() is now implemented as a wrapper around sqlite_compile() and sqlite_step().
  • The built-in min() and max() functions now honor the difference between NUMERIC and TEXT datatypes. Formerly, min() and max() always assumed their arguments were of type NUMERIC.
  • New HH:MM:SS modifier to the built-in date/time functions.
  • Experimental sqlite_last_statement_changes() API added. Fixed the the last_insert_rowid() function so that it works correctly with triggers.
  • Add functions prototypes for the database encryption API.
  • Fix several nuisance bugs.
2004 February 8 (2.8.12)

  • Fix a bug that will might corrupt the rollback journal if a power failure or external program halt occurs in the middle of a COMMIT. The corrupt journal can lead to database corruption when it is rolled back.
  • Reduce the size and increase the speed of various modules, especially the virtual machine.
  • Allow "<expr> IN <table>" as a shorthand for "<expr> IN (SELECT * FROM <table>".
  • Optimizations to the sqlite_mprintf() routine.
  • Make sure the MIN() and MAX() optimizations work within subqueries.
2004 January 14 (2.8.11)

  • Fix a bug in how the IN operator handles NULLs in subqueries. The bug was introduced by the previous release.
2004 January 13 (2.8.10)

  • Fix a potential database corruption problem on Unix caused by the fact that all posix advisory locks are cleared whenever you close() a file. The work around it to embargo all close() calls while locks are outstanding.
  • Performance enhancements on some corner cases of COUNT(*).
  • Make sure the in-memory backend response sanely if malloc() fails.
  • Allow sqlite_exec() to be called from within user-defined SQL functions.
  • Improved accuracy of floating-point conversions using "long double".
  • Bug fixes in the experimental date/time functions.
2004 January 5 (2.8.9)

  • Fix a 32-bit integer overflow problem that could result in corrupt indices in a database if large negative numbers (less than -2147483648) were inserted into a indexed numeric column.
  • Fix a locking problem on multi-threaded Linux implementations.
  • Always use "." instead of "," as the decimal point even if the locale requests ",".
  • Added UTC to localtime conversions to the experimental date/time functions.
  • Bug fixes to date/time functions.
2003 December 17 (2.8.8)

  • Fix a critical bug introduced into 2.8.0 which could cause database corruption.
  • Fix a problem with 3-way joins that do not use indices
  • The VACUUM command now works with the non-callback API
  • Improvements to the "PRAGMA integrity_check" command
2003 December 4 (2.8.7)

  • Added experimental sqlite_bind() and sqlite_reset() APIs.
  • If the name of the database is an empty string, open a new database in a temporary file that is automatically deleted when the database is closed.
  • Performance enhancements in the lemon-generated parser
  • Experimental date/time functions revised.
  • Disallow temporary indices on permanent tables.
  • Documentation updates and typo fixes
  • Added experimental sqlite_progress_handler() callback API
  • Removed support for the Oracle8 outer join syntax.
  • Allow GLOB and LIKE operators to work as functions.
  • Other minor documentation and makefile changes and bug fixes.
2003 August 21 (2.8.6)

  • Moved the CVS repository to www.sqlite.org
  • Update the NULL-handling documentation.
  • Experimental date/time functions added.
  • Bug fix: correctly evaluate a view of a view without segfaulting.
  • Bug fix: prevent database corruption if you dropped a trigger that had the same name as a table.
  • Bug fix: allow a VACUUM (without segfaulting) on an empty database after setting the EMPTY_RESULT_CALLBACKS pragma.
  • Bug fix: if an integer value will not fit in a 32-bit int, store it in a double instead.
  • Bug fix: Make sure the journal file directory entry is committed to disk before writing the database file.
2003 July 22 (2.8.5)

  • Make LIMIT work on a compound SELECT statement.
  • LIMIT 0 now shows no rows. Use LIMIT -1 to see all rows.
  • Correctly handle comparisons between an INTEGER PRIMARY KEY and a floating point number.
  • Fix several important bugs in the new ATTACH and DETACH commands.
  • Updated the NULL-handling document.
  • Allow NULL arguments in sqlite_compile() and sqlite_step().
  • Many minor bug fixes

2003 June 29 (2.8.4)

  • Enhanced the "PRAGMA integrity_check" command to verify indices.
  • Added authorization hooks for the new ATTACH and DETACH commands.
  • Many documentation updates
  • Many minor bug fixes
2003 June 4 (2.8.3)

  • Fix a problem that will corrupt the indices on a table if you do an INSERT OR REPLACE or an UPDATE OR REPLACE on a table that contains an INTEGER PRIMARY KEY plus one or more indices.
  • Fix a bug in windows locking code so that locks work correctly when simultaneously accessed by Win95 and WinNT systems.
  • Add the ability for INSERT and UPDATE statements to refer to the "rowid" (or "_rowid_" or "oid") columns.
  • Other important bug fixes
2003 May 17 (2.8.2)

  • Fix a problem that will corrupt the database file if you drop a table from the main database that has a TEMP index.
2003 May 16 (2.8.1)

  • Reactivated the VACUUM command that reclaims unused disk space in a database file.
  • Added the ATTACH and DETACH commands to allow interacting with multiple database files at the same time.
  • Added support for TEMP triggers and indices.
  • Added support for in-memory databases.
  • Removed the experimental sqlite_open_aux_file(). Its function is subsumed in the new ATTACH command.
  • The precedence order for ON CONFLICT clauses was changed so that ON CONFLICT clauses on BEGIN statements have a higher precedence than ON CONFLICT clauses on constraints.
  • Many, many bug fixes and compatibility enhancements.
2003 Feb 16 (2.8.0)

  • Modified the journal file format to make it more resistant to corruption that can occur after an OS crash or power failure.
  • Added a new C/C++ API that does not use callback for returning data.
2003 Jan 25 (2.7.6)

  • Performance improvements. The library is now much faster.
  • Added the sqlite_set_authorizer() API. Formal documentation has not been written - see the source code comments for instructions on how to use this function.
  • Fix a bug in the GLOB operator that was preventing it from working with upper-case letters.
  • Various minor bug fixes.
2002 Dec 27 (2.7.5)

  • Fix an uninitialized variable in pager.c which could (with a probability of about 1 in 4 billion) result in a corrupted database.
2002 Dec 17 (2.7.4)

  • Database files can now grow to be up to 2^41 bytes. The old limit was 2^31 bytes.
  • The optimizer will now scan tables in the reverse if doing so will satisfy an ORDER BY ... DESC clause.
  • The full pathname of the database file is now remembered even if a relative path is passed into sqlite_open(). This allows the library to continue operating correctly after a chdir().
  • Speed improvements in the VDBE.
  • Lots of little bug fixes.
2002 Oct 30 (2.7.3)

  • Various compiler compatibility fixes.
  • Fix a bug in the "expr IN ()" operator.
  • Accept column names in parentheses.
  • Fix a problem with string memory management in the VDBE
  • Fix a bug in the "table_info" pragma"
  • Export the sqlite_function_type() API function in the Windows DLL
  • Fix locking behavior under windows
  • Fix a bug in LEFT OUTER JOIN
2002 Sep 25 (2.7.2)

  • Prevent journal file overflows on huge transactions.
  • Fix a memory leak that occurred when sqlite_open() failed.
  • Honor the ORDER BY and LIMIT clause of a SELECT even if the result set is used for an INSERT.
  • Do not put write locks on the file used to hold TEMP tables.
  • Added documentation on SELECT DISTINCT and on how SQLite handles NULLs.
  • Fix a problem that was causing poor performance when many thousands of SQL statements were executed by a single sqlite_exec() call.
2002 Aug 31 (2.7.1)

  • Fix a bug in the ORDER BY logic that was introduced in version 2.7.0
  • C-style comments are now accepted by the tokenizer.
  • INSERT runs a little faster when the source is a SELECT statement.
2002 Aug 25 (2.7.0)

  • Make a distinction between numeric and text values when sorting. Text values sort according to memcmp(). Numeric values sort in numeric order.
  • Allow multiple simultaneous readers under windows by simulating the reader/writers locks that are missing from Win95/98/ME.
  • An error is now returned when trying to start a transaction if another transaction is already active.
2002 Aug 12 (2.6.3)

  • Add the ability to read both little-endian and big-endian databases. So database created under SunOS or MacOSX can be read and written under Linux or Windows and vice versa.
  • Convert to the new website: http://www.sqlite.org/
  • Allow transactions to span Linux Threads
  • Bug fix in the processing of the ORDER BY clause for GROUP BY queries
2002 Jly 30 (2.6.2)

  • Text files read by the COPY command can now have line terminators of LF, CRLF, or CR.
  • SQLITE_BUSY is handled correctly if encountered during database initialization.
  • Fix to UPDATE triggers on TEMP tables.
  • Documentation updates.
2002 Jly 19 (2.6.1)

  • Include a static string in the library that responds to the RCS "ident" command and which contains the library version number.
  • Fix an assertion failure that occurred when deleting all rows of a table with the "count_changes" pragma turned on.
  • Better error reporting when problems occur during the automatic 2.5.6 to 2.6.0 database format upgrade.
2002 Jly 17 (2.6.0)

  • Change the format of indices to correct a design flaw the originated with version 2.1.0. *** This is an incompatible file format change *** When version 2.6.0 or later of the library attempts to open a database file created by version 2.5.6 or earlier, it will automatically and irreversibly convert the file format. Make backup copies of older database files before opening them with version 2.6.0 of the library.

2002 Jly 7 (2.5.6)

  • Fix more problems with rollback. Enhance the test suite to exercise the rollback logic extensively in order to prevent any future problems.

2002 Jly 6 (2.5.5)

  • Fix a bug which could cause database corruption during a rollback. This bugs was introduced in version 2.4.0 by the freelist optimization of checking [410].
  • Fix a bug in aggregate functions for VIEWs.
  • Other minor changes and enhancements.
2002 Jly 1 (2.5.4)

  • Make the "AS" keyword optional again.
  • The datatype of columns now appear in the 4th argument to the callback.
  • Added the sqlite_open_aux_file() API, though it is still mostly undocumented and untested.
  • Added additional test cases and fixed a few bugs that those test cases found.
2002 Jun 24 (2.5.3)

  • Bug fix: Database corruption can occur due to the optimization that was introduced in version 2.4.0 (check-in [410]). The problem should now be fixed. The use of versions 2.4.0 through 2.5.2 is not recommended.
2002 Jun 24 (2.5.2)

  • Added the new SQLITE_TEMP_MASTER table which records the schema for temporary tables in the same way that SQLITE_MASTER does for persistent tables.
  • Added an optimization to UNION ALL
  • Fixed a bug in the processing of LEFT OUTER JOIN
  • The LIMIT clause now works on subselects
  • ORDER BY works on subselects
  • There is a new TypeOf() function used to determine if an expression is numeric or text.
  • Autoincrement now works for INSERT from a SELECT.
2002 Jun 19 (2.5.1)

  • The query optimizer now attempts to implement the ORDER BY clause using an index. Sorting is still used if not suitable index is available.
2002 Jun 17 (2.5.0)

  • Added support for row triggers.
  • Added SQL-92 compliant handling of NULLs.
  • Add support for the full SQL-92 join syntax and LEFT OUTER JOINs.
  • Double-quoted strings interpreted as column names not text literals.
  • Parse (but do not implement) foreign keys.
  • Performance improvements in the parser, pager, and WHERE clause code generator.
  • Make the LIMIT clause work on subqueries. (ORDER BY still does not work, though.)
  • Added the "%Q" expansion to sqlite_*_printf().
  • Bug fixes too numerous to mention (see the change log).
2002 May 09 (2.4.12)

  • Added logic to detect when the library API routines are called out of sequence.
2002 May 08 (2.4.11)

  • Bug fix: Column names in the result set were not being generated correctly for some (rather complex) VIEWs. This could cause a segfault under certain circumstances.
2002 May 02 (2.4.10)

  • Bug fix: Generate correct column headers when a compound SELECT is used as a subquery.
  • Added the sqlite_encode_binary() and sqlite_decode_binary() functions to the source tree. But they are not yet linked into the library.
  • Documentation updates.
  • Export the sqlite_changes() function from windows DLLs.
  • Bug fix: Do not attempt the subquery flattening optimization on queries that lack a FROM clause. To do so causes a segfault.
2002 Apr 21 (2.4.9)

  • Fix a bug that was causing the precompiled binary of SQLITE.EXE to report "out of memory" under Windows 98.
2002 Apr 20 (2.4.8)

  • Make sure VIEWs are created after their corresponding TABLEs in the output of the .dump command in the shell.
  • Speed improvements: Do not do synchronous updates on TEMP tables.
  • Many improvements and enhancements to the shell.
  • Make the GLOB and LIKE operators functions that can be overridden by a programmer. This allows, for example, the LIKE operator to be changed to be case sensitive.
2002 Apr 06 (2.4.7)

  • Add the ability to put TABLE.* in the column list of a SELECT statement.
  • Permit SELECT statements without a FROM clause.
  • Added the last_insert_rowid() SQL function.
  • Do not count rows where the IGNORE conflict resolution occurs in the row count.
  • Make sure functions expressions in the VALUES clause of an INSERT are correct.
  • Added the sqlite_changes() API function to return the number of row that changed in the most recent operation.
2002 Apr 02 (2.4.6)

  • Bug fix: Correctly handle terms in the WHERE clause of a join that do not contain a comparison operator.
2002 Apr 01 (2.4.5)

  • Bug fix: Correctly handle functions that appear in the WHERE clause of a join.
  • When the PRAGMA vdbe_trace=ON is set, correctly print the P3 operand value when it is a pointer to a structure rather than a pointer to a string.
  • When inserting an explicit NULL into an INTEGER PRIMARY KEY, convert the NULL value into a unique key automatically.
2002 Mar 24 (2.4.4)

  • Allow "VIEW" to be a column name
  • Added support for CASE expressions (patch from Dan Kennedy)
  • Added RPMS to the delivery (patches from Doug Henry)
  • Fix typos in the documentation
  • Cut over configuration management to a new CVS repository with its own CVSTrac bug tracking system.
2002 Mar 22 (2.4.3)

  • Fix a bug in SELECT that occurs when a compound SELECT is used as a subquery in the FROM of a SELECT.
  • The sqlite_get_table() function now returns an error if you give it two or more SELECTs that return different numbers of columns.
2002 Mar 14 (2.4.2)

  • Bug fix: Fix an assertion failure that occurred when ROWID was a column in a SELECT statement on a view.
  • Bug fix: Fix an uninitialized variable in the VDBE that would could an assert failure.
  • Make the os.h header file more robust in detecting when the compile is for windows and when it is for unix.
2002 Mar 13 (2.4.1)

  • Using an unnamed subquery in a FROM clause would cause a segfault.
  • The parser now insists on seeing a semicolon or the end of input before executing a statement. This avoids an accidental disaster if the WHERE keyword is misspelled in an UPDATE or DELETE statement.
2002 Mar 10 (2.4.0)

  • Change the name of the sanity_check PRAGMA to integrity_check and make it available in all compiles.
  • SELECT min() or max() of an indexed column with no WHERE or GROUP BY clause is handled as a special case which avoids a complete table scan.
  • Automatically generated ROWIDs are now sequential.
  • Do not allow dot-commands of the command-line shell to occur in the middle of a real SQL command.
  • Modifications to the "lemon" parser generator so that the parser tables are 4 times smaller.
  • Added support for user-defined functions implemented in C.
  • Added support for new functions: coalesce(), lower(), upper(), and random()
  • Added support for VIEWs.
  • Added the subquery flattening optimizer.
  • Modified the B-Tree and Pager modules so that disk pages that do not contain real data (free pages) are not journaled and are not written from memory back to the disk when they change. This does not impact database integrity, since the pages contain no real data, but it does make large INSERT operations about 2.5 times faster and large DELETEs about 5 times faster.
  • Made the CACHE_SIZE pragma persistent
  • Added the SYNCHRONOUS pragma
  • Fixed a bug that was causing updates to fail inside of transactions when the database contained a temporary table.
2002 Feb 18 (2.3.3)

  • Allow identifiers to be quoted in square brackets, for compatibility with MS-Access.
  • Added support for sub-queries in the FROM clause of a SELECT.
  • More efficient implementation of sqliteFileExists() under Windows. (by Joel Luscy)
  • The VALUES clause of an INSERT can now contain expressions, including scalar SELECT clauses.
  • Added support for CREATE TABLE AS SELECT
  • Bug fix: Creating and dropping a table all within a single transaction was not working.
2002 Feb 14 (2.3.2)

  • Bug fix: There was an incorrect assert() in pager.c. The real code was all correct (as far as is known) so everything should work OK if you compile with -DNDEBUG=1. When asserts are not disabled, there could be a fault.
2002 Feb 13 (2.3.1)

  • Bug fix: An assertion was failing if "PRAGMA full_column_names=ON;" was set and you did a query that used a rowid, like this: "SELECT rowid, * FROM ...".
2002 Jan 30 (2.3.0)

  • Fix a serious bug in the INSERT command which was causing data to go into the wrong columns if the data source was a SELECT and the INSERT clauses specified its columns in some order other than the default.
  • Added the ability to resolve constraint conflicts is ways other than an abort and rollback. See the documentation on the "ON CONFLICT" clause for details.
  • Temporary files are now automatically deleted by the operating system when closed. There are no more dangling temporary files on a program crash. (If the OS crashes, fsck will delete the file after reboot under Unix. I do not know what happens under Windows.)
  • NOT NULL constraints are honored.
  • The COPY command puts NULLs in columns whose data is '\N'.
  • In the COPY command, backslash can now be used to escape a newline.
  • Added the SANITY_CHECK pragma.
2002 Jan 28 (2.2.5)

  • Important bug fix: the IN operator was not working if either the left-hand or right-hand side was derived from an INTEGER PRIMARY KEY.
  • Do not escape the backslash '\' character in the output of the sqlite command-line access program.
2002 Jan 22 (2.2.4)

  • The label to the right of an AS in the column list of a SELECT can now be used as part of an expression in the WHERE, ORDER BY, GROUP BY, and/or HAVING clauses.
  • Fix a bug in the -separator command-line option to the sqlite command.
  • Fix a problem with the sort order when comparing upper-case strings against characters greater than 'Z' but less than 'a'.
  • Report an error if an ORDER BY or GROUP BY expression is constant.
2002 Jan 16 (2.2.3)

  • Fix warning messages in VC++ 7.0. (Patches from nicolas352001)
  • Make the library thread-safe. (The code is there and appears to work but has not been stressed.)
  • Added the new sqlite_last_insert_rowid() API function.
2002 Jan 13 (2.2.2)

  • Bug fix: An assertion was failing when a temporary table with an index had the same name as a permanent table created by a separate process.
  • Bug fix: Updates to tables containing an INTEGER PRIMARY KEY and an index could fail.
2002 Jan 9 (2.2.1)

  • Bug fix: An attempt to delete a single row of a table with a WHERE clause of "ROWID=x" when no such rowid exists was causing an error.
  • Bug fix: Passing in a NULL as the 3rd parameter to sqlite_open() would sometimes cause a coredump.
  • Bug fix: DROP TABLE followed by a CREATE TABLE with the same name all within a single transaction was causing a coredump.
  • Makefile updates from A. Rottmann
2001 Dec 22 (2.2.0)

  • Columns of type INTEGER PRIMARY KEY are actually used as the primary key in underlying B-Tree representation of the table.
  • Several obscure, unrelated bugs were found and fixed while implemented the integer primary key change of the previous bullet.
  • Added the ability to specify "*" as part of a larger column list in the result section of a SELECT statement. For example: "SELECT rowid, * FROM table1;".
  • Updates to comments and documentation.
2001 Dec 14 (2.1.7)

  • Fix a bug in CREATE TEMPORARY TABLE which was causing the table to be initially allocated in the main database file instead of in the separate temporary file. This bug could cause the library to suffer an assertion failure and it could cause "page leaks" in the main database file.
  • Fix a bug in the b-tree subsystem that could sometimes cause the first row of a table to be repeated during a database scan.
2001 Dec 14 (2.1.6)

  • Fix the locking mechanism yet again to prevent sqlite_exec() from returning SQLITE_PROTOCOL unnecessarily. This time the bug was a race condition in the locking code. This change effects both POSIX and Windows users.
2001 Dec 6 (2.1.5)

  • Fix for another problem (unrelated to the one fixed in 2.1.4) that sometimes causes sqlite_exec() to return SQLITE_PROTOCOL unnecessarily. This time the bug was in the POSIX locking code and should not effect windows users.
2001 Dec 4 (2.1.4)

  • Sometimes sqlite_exec() would return SQLITE_PROTOCOL when it should have returned SQLITE_BUSY.
  • The fix to the previous bug uncovered a deadlock which was also fixed.
  • Add the ability to put a single .command in the second argument of the sqlite shell
  • Updates to the FAQ
2001 Nov 23 (2.1.3)

  • Fix the behavior of comparison operators (ex: "<", "==", etc.) so that they are consistent with the order of entries in an index.
  • Correct handling of integers in SQL expressions that are larger than what can be represented by the machine integer.
2001 Nov 22 (2.1.2)

  • Changes to support 64-bit architectures.
  • Fix a bug in the locking protocol.
  • Fix a bug that could (rarely) cause the database to become unreadable after a DROP TABLE due to corruption to the SQLITE_MASTER table.
  • Change the code so that version 2.1.1 databases that were rendered unreadable by the above bug can be read by this version of the library even though the SQLITE_MASTER table is (slightly) corrupted.
2001 Nov 13 (2.1.1)

  • Bug fix: Sometimes arbitrary strings were passed to the callback function when the actual value of a column was NULL.
2001 Nov 12 (2.1.0)

  • Change the format of data records so that records up to 16MB in size can be stored.
  • Change the format of indices to allow for better query optimization.
  • Implement the "LIMIT ... OFFSET ..." clause on SELECT statements.
2001 Nov 3 (2.0.8)

  • Made selected parameters in API functions const. This should be fully backwards compatible.
  • Documentation updates
  • Simplify the design of the VDBE by restricting the number of sorters and lists to 1. In practice, no more than one sorter and one list was ever used anyhow.

2001 Oct 21 (2.0.7)

  • Any UTF-8 character or ISO8859 character can be used as part of an identifier.
  • Patches from Christian Werner to improve ODBC compatibility and to fix a bug in the round() function.
  • Plug some memory leaks that use to occur if malloc() failed. We have been and continue to be memory leak free as long as malloc() works.
  • Changes to some test scripts so that they work on Windows in addition to Unix.
2001 Oct 19 (2.0.6)

  • Added the EMPTY_RESULT_CALLBACKS pragma
  • Support for UTF-8 and ISO8859 characters in column and table names.
  • Bug fix: Compute correct table names with the FULL_COLUMN_NAMES pragma is turned on.
2001 Oct 14 (2.0.5)

  • Added the COUNT_CHANGES pragma.
  • Changes to the FULL_COLUMN_NAMES pragma to help out the ODBC driver.
  • Bug fix: "SELECT count(*)" was returning NULL for empty tables. Now it returns 0.
2001 Oct 13 (2.0.4)

  • Bug fix: an obscure and relatively harmless bug was causing one of the tests to fail when gcc optimizations are turned on. This release fixes the problem.
2001 Oct 13 (2.0.3)

  • Bug fix: the sqlite_busy_timeout() function was delaying 1000 times too long before failing.
  • Bug fix: an assertion was failing if the disk holding the database file became full or stopped accepting writes for some other reason. New tests were added to detect similar problems in the future.
  • Added new operators: & (bitwise-and) | (bitwise-or), ~ (ones-complement), << (shift left), >> (shift right).
  • Added new functions: round() and abs().
2001 Oct 9 (2.0.2)

  • Fix two bugs in the locking protocol. (One was masking the other.)
  • Removed some unused "#include " that were causing problems for VC++.
  • Fixed sqlite.h so that it is usable from C++
  • Added the FULL_COLUMN_NAMES pragma. When set to "ON", the names of columns are reported back as TABLE.COLUMN instead of just COLUMN.
  • Added the TABLE_INFO() and INDEX_INFO() pragmas to help support the ODBC interface.
  • Added support for TEMPORARY tables and indices.
2001 Oct 2 (2.0.1)

  • Remove some C++ style comments from btree.c so that it will compile using compilers other than gcc.
  • The ".dump" output from the shell does not work if there are embedded newlines anywhere in the data. This is an old bug that was carried forward from version 1.0. To fix it, the ".dump" output no longer uses the COPY command. It instead generates INSERT statements.
  • Extend the expression syntax to support "expr NOT NULL" (with a space between the "NOT" and the "NULL") in addition to "expr NOTNULL" (with no space).
2001 Sep 28 (2.0.0)

  • Automatically build binaries for Linux and Windows and put them on the website.

2001 Sep 28 (2.0-alpha-4)

  • Incorporate makefile patches form A. Rottmann to use LIBTOOL

2001 Sep 27 (2.0-alpha-3)

  • SQLite now honors the UNIQUE keyword in CREATE UNIQUE INDEX. Primary keys are required to be unique.
  • File format changed back to what it was for alpha-1
  • Fixes to the rollback and locking behavior

2001 Sep 20 (2.0-alpha-2)

  • Initial release of version 2.0. The idea of renaming the library to "SQLus" was abandoned in favor of keeping the "SQLite" name and bumping the major version number.
  • The pager and btree subsystems added back. They are now the only available backend.
  • The Dbbe abstraction and the GDBM and memory drivers were removed.
  • Copyright on all code was disclaimed. The library is now in the public domain.
2001 Jul 23 (1.0.32)

  • Pager and btree subsystems removed. These will be used in a follow-on SQL server library named "SQLus".
  • Add the ability to use quoted strings as table and column names in expressions.
2001 Apr 14 (1.0.31)

  • Pager subsystem added but not yet used.
  • More robust handling of out-of-memory errors.
  • New tests added to the test suite.
2001 Apr 6 (1.0.30)

  • Remove the sqlite_encoding TCL variable that was introduced in the previous version.
  • Add options -encoding and -tcl-uses-utf to the sqlite TCL command.
  • Add tests to make sure that tclsqlite was compiled using Tcl header files and libraries that match.
2001 Apr 5 (1.0.29)

  • The library now assumes data is stored as UTF-8 if the --enable-utf8 option is given to configure. The default behavior is to assume iso8859-x, as it has always done. This only makes a difference for LIKE and GLOB operators and the LENGTH and SUBSTR functions.
  • If the library is not configured for UTF-8 and the Tcl library is one of the newer ones that uses UTF-8 internally, then a conversion from UTF-8 to iso8859 and back again is done inside the TCL interface.
2001 Apr 4 (1.0.28)

  • Added limited support for transactions. At this point, transactions will do table locking on the GDBM backend. There is no support (yet) for rollback or atomic commit.
  • Added special column names ROWID, OID, and _ROWID_ that refer to the unique random integer key associated with every row of every table.
  • Additional tests added to the regression suite to cover the new ROWID feature and the TCL interface bugs mentioned below.
  • Changes to the "lemon" parser generator to help it work better when compiled using MSVC.
  • Bug fixes in the TCL interface identified by Oleg Oleinick.
2001 Mar 20 (1.0.27)

  • When doing DELETE and UPDATE, the library used to write the record numbers of records to be deleted or updated into a temporary file. This is changed so that the record numbers are held in memory.
  • The DELETE command without a WHILE clause just removes the database files from the disk, rather than going through and deleting record by record.
2001 Mar 20 (1.0.26)

  • A serious bug fixed on Windows. Windows users should upgrade. No impact to Unix.
2001 Mar 15 (1.0.25)

  • Modify the test scripts to identify tests that depend on system load and processor speed and to warn the user that a failure of one of those (rare) tests does not necessarily mean the library is malfunctioning. No changes to code.

2001 Mar 14 (1.0.24)

  • Fix a bug which was causing the UPDATE command to fail on systems where "malloc(0)" returns NULL. The problem does not appear Windows, Linux, or HPUX but does cause the library to fail on QNX.

2001 Feb 19 (1.0.23)

  • An unrelated (and minor) bug from Mark Muranwski fixed. The algorithm for figuring out where to put temporary files for a "memory:" database was not working quite right.

2001 Feb 19 (1.0.22)

  • The previous fix was not quite right. This one seems to work better.

2001 Feb 19 (1.0.21)

  • The UPDATE statement was not working when the WHERE clause contained some terms that could be satisfied using indices and other terms that could not. Fixed.
2001 Feb 11 (1.0.20)

  • Merge development changes into the main trunk. Future work toward using a BTree file structure will use a separate CVS source tree. This CVS tree will continue to support the GDBM version of SQLite only.
2001 Feb 6 (1.0.19)

  • Fix a strange (but valid) C declaration that was causing problems for QNX. No logical changes.
2001 Jan 4 (1.0.18)

  • Print the offending SQL statement when an error occurs.
  • Do not require commas between constraints in CREATE TABLE statements.
  • Added the "-echo" option to the shell.
  • Changes to comments.
2000 Dec 10 (1.0.17)

  • Rewrote sqlite_complete() to make it faster.
  • Minor tweaks to other code to make it run a little faster.
  • Added new tests for sqlite_complete() and for memory leaks.
2000 Dec 4 (1.0.16)

  • Documentation updates. Mostly fixing of typos and spelling errors.
2000 Oct 23 (1.0.15)

  • Documentation updates
  • Some sanity checking code was removed from the inner loop of vdbe.c to help the library to run a little faster. The code is only removed if you compile with -DNDEBUG.
2000 Oct 19 (1.0.14)

  • Added a "memory:" backend driver that stores its database in an in-memory hash table.
2000 Oct 18 (1.0.13)

  • Break out the GDBM driver into a separate file in anticipation to added new drivers.
  • Allow the name of a database to be prefixed by the driver type. For now, the only driver type is "gdbm:".
2000 Oct 16 (1.0.12)

  • Fixed an off-by-one error that was causing a coredump in the '%q' format directive of the new sqlite_..._printf() routines.
  • Added the sqlite_interrupt() interface.
  • In the shell, sqlite_interrupt() is invoked when the user presses Control-C
  • Fixed some instances where sqlite_exec() was returning the wrong error code.
2000 Oct 11 (1.0.10)

  • Added notes on how to compile for Windows95/98.
  • Removed a few variables that were not being used. Etc.
2000 Oct 8 (1.0.9)

  • Added the sqlite_..._printf() interface routines.
  • Modified the sqlite shell program to use the new interface routines.
  • Modified the sqlite shell program to print the schema for the built-in SQLITE_MASTER table, if explicitly requested.
2000 Sep 30 (1.0.8)

  • Begin writing documentation on the TCL interface.

2000 Sep 29 (Not Released)

  • Added the sqlite_get_table() API
  • Updated the documentation for due to the above change.
  • Modified the sqlite shell to make use of the new sqlite_get_table() API in order to print a list of tables in multiple columns, similar to the way "ls" prints filenames.
  • Modified the sqlite shell to print a semicolon at the end of each CREATE statement in the output of the ".schema" command.

2000 Sep 21 (Not Released)

  • Change the tclsqlite "eval" method to return a list of results if no callback script is specified.
  • Change tclsqlite.c to use the Tcl_Obj interface
  • Add tclsqlite.c to the libsqlite.a library

2000 Sep 13 (Version 1.0.5)

  • Changed the print format for floating point values from "%g" to "%.15g".
  • Changed the comparison function so that numbers in exponential notation (ex: 1.234e+05) sort in numerical order.

2000 Aug 28 (Version 1.0.4)

  • Added functions length() and substr().
  • Fix a bug in the sqlite shell program that was causing a coredump when the output mode was "column" and the first row of data contained a NULL.

2000 Aug 22 (Version 1.0.3)

  • In the sqlite shell, print the "Database opened READ ONLY" message to stderr instead of stdout.
  • In the sqlite shell, now print the version number on initial startup.
  • Add the sqlite_version[] string constant to the library
  • Makefile updates
  • Bug fix: incorrect VDBE code was being generated for the following circumstance: a query on an indexed table containing a WHERE clause with an IN operator that had a subquery on its right-hand side.

2000 Aug 18 (Version 1.0.1)

  • Fix a bug in the configure script.
  • Minor revisions to the website.

2000 Aug 17 (Version 1.0)

  • Change the sqlite program so that it can read databases for which it lacks write permission. (It used to refuse all access if it could not write.)

2000 Aug 9

  • Treat carriage returns as white space.

2000 Aug 8

  • Added pattern matching to the ".table" command in the "sqlite" command shell.

2000 Aug 4

  • Documentation updates
  • Added "busy" and "timeout" methods to the Tcl interface

2000 Aug 3

  • File format version number was being stored in sqlite_master.tcl multiple times. This was harmless, but unnecessary. It is now fixed.

2000 Aug 2

  • The file format for indices was changed slightly in order to work around an inefficiency that can sometimes come up with GDBM when there are large indices having many entries with the same key. ** Incompatible Change **

2000 Aug 1

  • The parser's stack was overflowing on a very long UPDATE statement. This is now fixed.

2000 July 31

  • Finish the VDBE tutorial.
  • Added documentation on compiling to WindowsNT.
  • Fix a configuration program for WindowsNT.
  • Fix a configuration problem for HPUX.

2000 July 29

  • Better labels on column names of the result.

2000 July 28

  • Added the sqlite_busy_handler() and sqlite_busy_timeout() interface.

2000 June 23

  • Begin writing the VDBE tutorial.

2000 June 21

  • Clean up comments and variable names. Changes to documentation. No functional changes to the code.

2000 June 19

  • Column names in UPDATE statements were case sensitive. This mistake has now been fixed.

2000 June 16

  • Added the concatenate string operator (||)

2000 June 12

  • Added the fcnt() function to the SQL interpreter. The fcnt() function returns the number of database "Fetch" operations that have occurred. This function is designed for use in test scripts to verify that queries are efficient and appropriately optimized. Fcnt() has no other useful purpose, as far as I know.
  • Added a bunch more tests that take advantage of the new fcnt() function. The new tests did not uncover any new problems.

2000 June 8

  • Added lots of new test cases
  • Fix a few bugs discovered while adding test cases
  • Begin adding lots of new documentation

2000 June 6

  • Added compound select operators: UNION, UNION ALL, INTERSECT, and EXCEPT
  • Added support for using (SELECT ...) within expressions
  • Added support for IN and BETWEEN operators
  • Added support for GROUP BY and HAVING
  • NULL values are now reported to the callback as a NULL pointer rather than an empty string.

2000 June 3

  • Added support for default values on columns of a table.
  • Improved test coverage. Fixed a few obscure bugs found by the improved tests.

2000 June 2

  • All database files to be modified by an UPDATE, INSERT or DELETE are now locked before any changes are made to any files. This makes it safe (I think) to access the same database simultaneously from multiple processes.
  • The code appears stable so we are now calling it "beta".

2000 June 1

  • Better support for file locking so that two or more processes (or threads) can access the same database simultaneously. More work needed in this area, though.

2000 May 31

  • Added support for aggregate functions (Ex: COUNT(*), MIN(...)) to the SELECT statement.
  • Added support for SELECT DISTINCT ...

2000 May 30

  • Added the LIKE operator.
  • Added a GLOB operator: similar to LIKE but it uses Unix shell globbing wildcards instead of the '%' and '_' wildcards of SQL.
  • Added the COPY command patterned after PostgreSQL so that SQLite can now read the output of the pg_dump database dump utility of PostgreSQL.
  • Added a VACUUM command that that calls the gdbm_reorganize() function on the underlying database files.
  • And many, many bug fixes...

2000 May 29

  • Initial Public Release of Alpha code


Query Language Understood by SQLite

SQL As Understood By SQLite

The SQLite library understands most of the standard SQL language. But it does omit some features while at the same time adding a few features of its own. This document attempts to describe precisely what parts of the SQL language SQLite does and does not support. A list of keywords is also provided.

In all of the syntax diagrams that follow, literal text is shown in bold blue. Non-terminal symbols are shown in italic red. Operators that are part of the syntactic markup itself are shown in black roman.

This document is just an overview of the SQL syntax implemented by SQLite. Many low-level productions are omitted. For detailed information on the language that SQLite understands, refer to the source code and the grammar file "parse.y".

SQLite implements the follow syntax:

  • ALTER TABLE
  • ANALYZE
  • ATTACH DATABASE
  • BEGIN TRANSACTION
  • comment
  • COMMIT TRANSACTION
  • COPY
  • CREATE INDEX
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE VIEW
  • DELETE
  • DETACH DATABASE
  • DROP INDEX
  • DROP TABLE
  • DROP TRIGGER
  • DROP VIEW
  • END TRANSACTION
  • EXPLAIN
  • expression
  • INSERT
  • ON CONFLICT clause
  • PRAGMA
  • REINDEX
  • REPLACE
  • ROLLBACK TRANSACTION
  • SELECT
  • UPDATE
  • VACUUM

Details on the implementation of each command are provided in the sequel.


SQLite home page

About SQLite

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine. Features include:

  • Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
  • Zero-configuration - no setup or administration needed.
  • Implements most of SQL92. (Features not supported)
  • A complete database is stored in a single disk file.
  • Database files can be freely shared between machines with different byte orders.
  • Supports databases up to 2 terabytes (241 bytes) in size.
  • Sizes of strings and BLOBs limited only by available memory.
  • Small code footprint: less than 250KiB fully configured or less than 150KiB with optional features omitted.
  • Faster than popular client/server database engines for most common operations.
  • Simple, easy to use API.
  • TCL bindings included. Bindings for many other languages available separately.
  • Well-commented source code with over 95% test coverage.
  • Self-contained: no external dependencies.
  • Sources are in the public domain. Use for any purpose.

The SQLite distribution comes with a standalone command-line access program (sqlite) that can be used to administer an SQLite database and which serves as an example of how to use the SQLite library.

News

2006-Apr-5 - Version 3.3.5

This release fixes many minor bugs and documentation typos and provides some minor new features and performance enhancements. Upgrade only if you are having problems or need one of the new features.

2006-Feb-11 - Version 3.3.4

This release fixes several bugs, including a a blunder that might cause a deadlock on multithreaded systems. Anyone using SQLite in a multithreaded environment should probably upgrade.

2006-Jan-31 - Version 3.3.3 stable

There have been no major problems discovered in version 3.3.2, so we hereby declare the new APIs and language features to be stable and supported.

2006-Jan-24 - Version 3.3.2 beta

More bug fixes and performance improvements as we move closer to a production-ready version 3.3.x.

2006-Jan-16 - Version 3.3.1 alpha

Many bugs found in last week's alpha release have now been fixed and the library is running much faster again.

Database connections can now be moved between threads as long as the connection holds no locks at the time it is moved. Thus the common paradigm of maintaining a pool of database connections and handing them off to transient worker threads is now supported. Please help test this new feature. See the MultiThreading wiki page for additional information.

2006-Jan-10 - Version 3.3.0 alpha

Version 3.3.0 adds support for CHECK constraints, DESC indices, separate REAL and INTEGER column affinities, a new OS interface layer design, and many other changes. The code passed a regression test but should still be considered alpha. Please report any problems.

The file format for version 3.3.0 has changed slightly in order provide a more efficient encoding of binary values. SQLite 3.3.0 will read and write legacy databases created with any prior version of SQLite 3. But databases created by version 3.3.0 will not be readable or writable by earlier versions of the SQLite. The older file format can be specified at compile-time for those rare cases where it is needed.

2005-Dec-19 - Versions 3.2.8 and 2.8.17

These versions contain one-line changes to 3.2.7 and 2.8.16 to fix a bug that has been present since March of 2002 and version 2.4.0. That bug might possibly cause database corruption if a large INSERT or UPDATE statement within a multi-statement transaction fails due to a uniqueness constraint but the containing transaction commits.

Old news...

This page last modified on 2006/04/05 12:50:08